![]() |
spreading equally
Help please.
I have two columns (B and C) of dates which represent start and end dates and column A that has a numeric data. Columns D and on are months (Jan through Dec). Is there a function that I can use that would spread the numeric data from column A in the months in columns D and on that B and C represents? TIA D Pingger |
spreading equally
One way ..
Try this sample from my archives: http://savefile.com/files/638334 Club_Membership_Fee_Apportionments_v3.xls [Fee Apportionments by no. of days within the month] -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "D Pingger" wrote: Help please. I have two columns (B and C) of dates which represent start and end dates and column A that has a numeric data. Columns D and on are months (Jan through Dec). Is there a function that I can use that would spread the numeric data from column A in the months in columns D and on that B and C represents? TIA D Pingger |
spreading equally
What should happen when the amount is not evenly divisable by the number of
months? For example: Amount = 1 Start date = 1/1/2007 End Date = 3/1/2007 One month has to be 0.34 and the other two would need to be 0.33. Columns D and on are months (Jan through Dec). Are these TEXT entries? I'm also assuming that the date interval does not span into a new year? For example: Start date = 6/1/2007 End date = 2/1/2008 -- Biff Microsoft Excel MVP "D Pingger" wrote in message ... Help please. I have two columns (B and C) of dates which represent start and end dates and column A that has a numeric data. Columns D and on are months (Jan through Dec). Is there a function that I can use that would spread the numeric data from column A in the months in columns D and on that B and C represents? TIA D Pingger |
spreading equally
Are these TEXT entries?
They are date entries mm/yy I'm also assuming that the date interval does not span into a new year? They do span into a new year. What should happen when the amount is not evenly divisable by the number of months? 3 to 4 place decimals should be sufficient. TIA D Pingger "T. Valko" wrote: What should happen when the amount is not evenly divisable by the number of months? For example: Amount = 1 Start date = 1/1/2007 End Date = 3/1/2007 One month has to be 0.34 and the other two would need to be 0.33. Columns D and on are months (Jan through Dec). Are these TEXT entries? I'm also assuming that the date interval does not span into a new year? For example: Start date = 6/1/2007 End date = 2/1/2008 -- Biff Microsoft Excel MVP "D Pingger" wrote in message ... Help please. I have two columns (B and C) of dates which represent start and end dates and column A that has a numeric data. Columns D and on are months (Jan through Dec). Is there a function that I can use that would spread the numeric data from column A in the months in columns D and on that B and C represents? TIA D Pingger |
spreading equally
Ok, I'm making a lot of assumptions here. That's why providing explicit
details is a good idea! (then I/we don't have to guess) A2 = some number B2 = start date C2 = end date D1:?? = monthly column headers. You say these are in the format mm/yy but what is their true date value? 08/07 is August 2007 but what DAY in August? Ambiguous dates, I hate 'em! <g My formula is based on these dates being the 1st of the month. So, 08/07 is 8/1/2007. I'm assuming that if you have a start date of 1/31/2007 you want a value returned in the monthly column for 01/07. Enter this formula in D2 and copy across as needed: =IF(AND($B2-DAY($B2)+1<=D1,D1<=$C2),$A2/(1+DATEDIF($B2,$C2,"y")*12+DATEDIF($B2-DAY($B2)+1,$C2,"ym")),"") -- Biff Microsoft Excel MVP "D Pingger" wrote in message ... Are these TEXT entries? They are date entries mm/yy I'm also assuming that the date interval does not span into a new year? They do span into a new year. What should happen when the amount is not evenly divisable by the number of months? 3 to 4 place decimals should be sufficient. TIA D Pingger "T. Valko" wrote: What should happen when the amount is not evenly divisable by the number of months? For example: Amount = 1 Start date = 1/1/2007 End Date = 3/1/2007 One month has to be 0.34 and the other two would need to be 0.33. Columns D and on are months (Jan through Dec). Are these TEXT entries? I'm also assuming that the date interval does not span into a new year? For example: Start date = 6/1/2007 End date = 2/1/2008 -- Biff Microsoft Excel MVP "D Pingger" wrote in message ... Help please. I have two columns (B and C) of dates which represent start and end dates and column A that has a numeric data. Columns D and on are months (Jan through Dec). Is there a function that I can use that would spread the numeric data from column A in the months in columns D and on that B and C represents? TIA D Pingger |
spreading equally
Thanks Mike and T.Valko.
Both of your suggestions worked and worked really well. This community is great. Thanks again, guys. D Pingger "T. Valko" wrote: Ok, I'm making a lot of assumptions here. That's why providing explicit details is a good idea! (then I/we don't have to guess) A2 = some number B2 = start date C2 = end date D1:?? = monthly column headers. You say these are in the format mm/yy but what is their true date value? 08/07 is August 2007 but what DAY in August? Ambiguous dates, I hate 'em! <g My formula is based on these dates being the 1st of the month. So, 08/07 is 8/1/2007. I'm assuming that if you have a start date of 1/31/2007 you want a value returned in the monthly column for 01/07. Enter this formula in D2 and copy across as needed: =IF(AND($B2-DAY($B2)+1<=D1,D1<=$C2),$A2/(1+DATEDIF($B2,$C2,"y")*12+DATEDIF($B2-DAY($B2)+1,$C2,"ym")),"") -- Biff Microsoft Excel MVP "D Pingger" wrote in message ... Are these TEXT entries? They are date entries mm/yy I'm also assuming that the date interval does not span into a new year? They do span into a new year. What should happen when the amount is not evenly divisable by the number of months? 3 to 4 place decimals should be sufficient. TIA D Pingger "T. Valko" wrote: What should happen when the amount is not evenly divisable by the number of months? For example: Amount = 1 Start date = 1/1/2007 End Date = 3/1/2007 One month has to be 0.34 and the other two would need to be 0.33. Columns D and on are months (Jan through Dec). Are these TEXT entries? I'm also assuming that the date interval does not span into a new year? For example: Start date = 6/1/2007 End date = 2/1/2008 -- Biff Microsoft Excel MVP "D Pingger" wrote in message ... Help please. I have two columns (B and C) of dates which represent start and end dates and column A that has a numeric data. Columns D and on are months (Jan through Dec). Is there a function that I can use that would spread the numeric data from column A in the months in columns D and on that B and C represents? TIA D Pingger |
spreading equally
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "D Pingger" wrote in message ... Thanks Mike and T.Valko. Both of your suggestions worked and worked really well. This community is great. Thanks again, guys. D Pingger "T. Valko" wrote: Ok, I'm making a lot of assumptions here. That's why providing explicit details is a good idea! (then I/we don't have to guess) A2 = some number B2 = start date C2 = end date D1:?? = monthly column headers. You say these are in the format mm/yy but what is their true date value? 08/07 is August 2007 but what DAY in August? Ambiguous dates, I hate 'em! <g My formula is based on these dates being the 1st of the month. So, 08/07 is 8/1/2007. I'm assuming that if you have a start date of 1/31/2007 you want a value returned in the monthly column for 01/07. Enter this formula in D2 and copy across as needed: =IF(AND($B2-DAY($B2)+1<=D1,D1<=$C2),$A2/(1+DATEDIF($B2,$C2,"y")*12+DATEDIF($B2-DAY($B2)+1,$C2,"ym")),"") -- Biff Microsoft Excel MVP "D Pingger" wrote in message ... Are these TEXT entries? They are date entries mm/yy I'm also assuming that the date interval does not span into a new year? They do span into a new year. What should happen when the amount is not evenly divisable by the number of months? 3 to 4 place decimals should be sufficient. TIA D Pingger "T. Valko" wrote: What should happen when the amount is not evenly divisable by the number of months? For example: Amount = 1 Start date = 1/1/2007 End Date = 3/1/2007 One month has to be 0.34 and the other two would need to be 0.33. Columns D and on are months (Jan through Dec). Are these TEXT entries? I'm also assuming that the date interval does not span into a new year? For example: Start date = 6/1/2007 End date = 2/1/2008 -- Biff Microsoft Excel MVP "D Pingger" wrote in message ... Help please. I have two columns (B and C) of dates which represent start and end dates and column A that has a numeric data. Columns D and on are months (Jan through Dec). Is there a function that I can use that would spread the numeric data from column A in the months in columns D and on that B and C represents? TIA D Pingger |
spreading equally
Thanks Mike and T.Valko.
Typo above. I'm Max, not Mike <g You're welcome ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "D Pingger" wrote in message ... Thanks Mike and T.Valko. Both of your suggestions worked and worked really well. This community is great. Thanks again, guys. D Pingger |
spreading equally
We can shorten the formula a little bit:
=IF(AND($B2-DAY($B2)+1<=D1,D1<=$C2),$A2/(1+DATEDIF($B2-DAY($B2)+1,$C2,"m")),"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok, I'm making a lot of assumptions here. That's why providing explicit details is a good idea! (then I/we don't have to guess) A2 = some number B2 = start date C2 = end date D1:?? = monthly column headers. You say these are in the format mm/yy but what is their true date value? 08/07 is August 2007 but what DAY in August? Ambiguous dates, I hate 'em! <g My formula is based on these dates being the 1st of the month. So, 08/07 is 8/1/2007. I'm assuming that if you have a start date of 1/31/2007 you want a value returned in the monthly column for 01/07. Enter this formula in D2 and copy across as needed: =IF(AND($B2-DAY($B2)+1<=D1,D1<=$C2),$A2/(1+DATEDIF($B2,$C2,"y")*12+DATEDIF($B2-DAY($B2)+1,$C2,"ym")),"") -- Biff Microsoft Excel MVP "D Pingger" wrote in message ... Are these TEXT entries? They are date entries mm/yy I'm also assuming that the date interval does not span into a new year? They do span into a new year. What should happen when the amount is not evenly divisable by the number of months? 3 to 4 place decimals should be sufficient. TIA D Pingger "T. Valko" wrote: What should happen when the amount is not evenly divisable by the number of months? For example: Amount = 1 Start date = 1/1/2007 End Date = 3/1/2007 One month has to be 0.34 and the other two would need to be 0.33. Columns D and on are months (Jan through Dec). Are these TEXT entries? I'm also assuming that the date interval does not span into a new year? For example: Start date = 6/1/2007 End date = 2/1/2008 -- Biff Microsoft Excel MVP "D Pingger" wrote in message ... Help please. I have two columns (B and C) of dates which represent start and end dates and column A that has a numeric data. Columns D and on are months (Jan through Dec). Is there a function that I can use that would spread the numeric data from column A in the months in columns D and on that B and C represents? TIA D Pingger |
All times are GMT +1. The time now is 05:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com