Natalie has attended:
Excel Advanced course
PowerPoint Intermediate Advanced course
Date Calculations
It relates to dates calculations
Start Date End Date Daily rate Monthly rate
01/12/11 31/03/12 50.00 75.00
20/12/11 20/04/12 45.00 80.00
1. Is the Start Date a full month? if not return the number of days including the start date and mulitiply by Daily rate.
Example Start date 20/12/2011 means that (31days -19day = 12day * daily rate)
Example: start date= 01/12/2011 mean it is a full month and should be multiplied by monthly rate.
2 Is End date a full month? if not multiply number of days into month by daily rate.
End date 20/04/2012, this means 20 days * daliy rate rate.
RE: Date Calculations
Hi Natalie
Thank you for your question. I have come up with a formula - it's rather long...
=IF(AND(A2=(DATE(YEAR(A2),MONTH(A2),1)),B2=(DATE(YEAR(B2),MONTH(B2)+1,0))),(B2-A2+1)*D2,(B2-A2+1)*C2)
where
A2 is the start date
B2 is the end date
C2 is the day rate
D2 is the month rate
To make it easier to test I have attached an example spreadsheet.
Would you be able to take a look and see if it produces the kind of thing you are looking for?
It used the date function and Excel's ability to work out day and month values with some logical functions If and And).
Kind regards,
Andrew
date calc example.xlsx
