date calculations

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Date Calculations

Date Calculations

resolvedResolved · Urgent Priority · Version 2007

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

Attached files...

date calc example.xlsx

 

Training courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Create Charts with One keystroke

Create a graph with one click

1. Select your data.
2. Press F11.
3. You have a graph.

View all Excel hints and tips


Server loaded in 0.12 secs.