Forum home » Delegate support and help forum » Microsoft Excel Training and help » Date Calculations
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Date Calculations
Resolved · Urgent Priority · Version 2007
Reply Wed 14 Dec 2011, 16:53Delegate Natalie said...
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.
For upcoming training course dates see: Pricing & availability
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
Attached files...
date calc example.xlsx
|