spreading equally

G

Guest

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
 
T

T. Valko

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
 
G

Guest

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

T. Valko

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")),"")
 
G

Guest

Thanks Mike and T.Valko.

Both of your suggestions worked and worked really well. This community is
great.

Thanks again, guys.

D Pingger
 
T

T. Valko

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")),"")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top