Stumped on Nested IF's using dates.

[Mathew P Bennett]

Good Evening All.
This problem has been bothering me for weeks now, & I can see no solution.
Any help on this would be very appreciated.
I have a table of data (simplified) as follows:

A B C D E F
G...
1 Start Date Finish Date Daily Rate (£) Apr 03 May 03 Jun 03 Jul 03
2 01/04/03 487.00
3 08/04/03 21/05/03 487.00
4 04/05/03 487.00
5 30/05/03 28/08/03 487.00
6 06/06/03 29/06/03 487.00
7 01/07/03 487.00
8 04/07/03 28/07/03 487.00


The wish is to populate Columns D to G... with the appropriate sum of the days at the daily rate. If Column B is blank then
one can assume complete months. (All dates are inclusive).

I have tried nested if's, but this becomes very complicated with also having to use AND, <, >, and also having to take into
account the blank cells in B.

Can anyone help, please!
I do hope this formats OK when posted.

Thank you. Yours Mathew.

 

[DavidP]

Hi Matthew

I have made a small modification to the spreadsheet to simplify the
macros greatly. I inserted a row below the headers and added the days
in each month. i.e. 30 in D2 & F2 and then 31 in E2 & G2.
Then post this formula into D3 and copy across the range

=IF($B3="",$C3*D$2,IF($B3<D$1,0,IF($B3>EOMONTH(D$1,0),$C3*D$2,($B3-D$1+1)*$C3)))

You could do it without the extra row I think, but far more complex.
If you wish just hide the extra row

 

[Earl Kiosterud]

Mathew,

I've put actual first-of-month dates in your headings (D1 and across).
Format them mmm yy to show month and year only. The following formula goes
in D2:

=MAX(0,(MIN($B2,EOMONTH(D$1,0))-$A2))*$C2

If the cell formats itself for date, change it to something like General.
Copy the cell down and across. You need the Analysis Toolpak installed for
the EOMONTH function to be available. This calculates based on all days in a
month. For workdays only, it will be different.

I think it's right. But test it thoroughly.

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Good Evening All.
This problem has been bothering me for weeks now, & I can see no solution.
Any help on this would be very appreciated.
I have a table of data (simplified) as follows:

A B C D E F
G...
1 Start Date Finish Date Daily Rate (£) Apr 03

May 03 Jun 03 Jul 03

2 01/04/03 487.00
3 08/04/03 21/05/03 487.00
4 04/05/03 487.00
5 30/05/03 28/08/03 487.00
6 06/06/03 29/06/03 487.00
7 01/07/03 487.00
8 04/07/03 28/07/03 487.00


The wish is to populate Columns D to G... with the appropriate sum of the

days at the daily rate. If Column B is blank then

 

[Mathew P Bennett]

David Hi.
Thank you for your reply. We have similar thinking. I do have the start & end dates for each month above each month, and was trying
to use these in my formulae.
I am trying your formula now.
I will post to let you know how it works for me.
Cheers again
Mathew
Hi Matthew

I have made a small modification to the spreadsheet to simplify the
macros greatly. I inserted a row below the headers and added the days
in each month. i.e. 30 in D2 & F2 and then 31 in E2 & G2.
Then post this formula into D3 and copy across the range

=IF($B3="",$C3*D$2,IF($B3<D$1,0,IF($B3>EOMONTH(D$1,0),$C3*D$2,($B3-D$1+1)*$C3)))

You could do it without the extra row I think, but far more complex.
If you wish just hide the extra row

 

[Mathew P Bennett]

Hi Earl,
Thank you for this, however it cumulates the values month-on-month, and does not provide discrete montly totals.
Just thought I ought to point this out.
Cheers for your time & efforts.
Thank Guys.
Mathew
Mathew,

I've put actual first-of-month dates in your headings (D1 and across).
Format them mmm yy to show month and year only. The following formula goes
in D2:

=MAX(0,(MIN($B2,EOMONTH(D$1,0))-$A2))*$C2

If the cell formats itself for date, change it to something like General.
Copy the cell down and across. You need the Analysis Toolpak installed for
the EOMONTH function to be available. This calculates based on all days in a
month. For workdays only, it will be different.

I think it's right. But test it thoroughly.

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Good Evening All.
This problem has been bothering me for weeks now, & I can see no solution.
Any help on this would be very appreciated.
I have a table of data (simplified) as follows:

A B C D E F
G...
1 Start Date Finish Date Daily Rate (£) Apr 03

May 03 Jun 03 Jul 03

2 01/04/03 487.00
3 08/04/03 21/05/03 487.00
4 04/05/03 487.00
5 30/05/03 28/08/03 487.00
6 06/06/03 29/06/03 487.00
7 01/07/03 487.00
8 04/07/03 28/07/03 487.00


The wish is to populate Columns D to G... with the appropriate sum of the

days at the daily rate. If Column B is blank then

 

[Mathew P Bennett]

Cheers Dave
Works Great. Just what I needed.
Mathew
Hi Matthew

I have made a small modification to the spreadsheet to simplify the
macros greatly. I inserted a row below the headers and added the days
in each month. i.e. 30 in D2 & F2 and then 31 in E2 & G2.
Then post this formula into D3 and copy across the range

=IF($B3="",$C3*D$2,IF($B3<D$1,0,IF($B3>EOMONTH(D$1,0),$C3*D$2,($B3-D$1+1)*$C3)))

You could do it without the extra row I think, but far more complex.
If you wish just hide the extra row

 

[Earl Kiosterud]

Mathew,

We take a lot of SWAGs in the newsgroups. You said SUM, and I gave you
month-to-date figures. Here's a modification for individual month figures.
If that's what you want.

=MAX(0,(MIN($B2,EOMONTH(D$1,0))-MAX($A2,D$1))+1)*$C2

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Hi Earl,
Thank you for this, however it cumulates the values month-on-month, and

does not provide discrete montly totals.

 

[Mathew P Bennett]

Hi Earl,
Fantastic, thank you very much.
Apologies for the vagueness of the original post/request.
I have been at this for what seems like ages now, have asked everyone I know who might be able to help, and this is the only
solution that I have received which actually works.(for me)
Cheers
By the way, what does SWAG(s) mean?
Appreciatively yours, Mathew

Mathew,

We take a lot of SWAGs in the newsgroups. You said SUM, and I gave you
month-to-date figures. Here's a modification for individual month figures.
If that's what you want.

=MAX(0,(MIN($B2,EOMONTH(D$1,0))-MAX($A2,D$1))+1)*$C2

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Hi Earl,
Thank you for this, however it cumulates the values month-on-month, and

does not provide discrete montly totals.

 

[Earl Kiosterud]

Mathew,

Glad it worked.

A SWAG is a scientific wild ***ed guess!

The formula calculates date differences, non-inclusive (May 1 to May 5 would
be 4 days). Seems to me I changed it so it'd count inclusive dates where
there's a full month (May 1 to May 31 would be 31 days). I don't know if
that's the one I posted. Check that it's doing right, and if not, I'll get
you the change.

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Hi Earl,
Fantastic, thank you very much.
Apologies for the vagueness of the original post/request.
I have been at this for what seems like ages now, have asked everyone I

know who might be able to help, and this is the only