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