Difference between two dates

[Guest]

Hi,

I need to express the difference between two dates in a decimal form correct
to two decimal points. However, if the two dates are the start and end of two
months, say, 1st March 2007 and 30th April 2007, I want the answer to appear
as 2.00 (being two complete months between the dates), and not as 1.98, which
is the answer Excel gives me. I cannot use the Round option because it rounds
off intermediate decimal figures to the nearest whole number. In other words,
while it rounds off 1.98 to 2.00 (desired), it also rounds off 0.67 to 1.00
(not desired). What formula will help me to achieve what I want? I don't want
to add a '+1' either. Is there anyway to get this? Thanks !!

 

[Pete_UK]

Chip Pearson has some notes on using the DATEDIF function here:

http://www.cpearson.com/excel/datedif.htm

Hope this helps.

Pete

 

[Guest]

would you want 1 march to 1 may as 2 also?
are you including both the first and last day in your duration?
I assume your 2 decimal places are for the months.
what equation do you use to account for the different number of days in
different months?

 

[Guest]

bj, thanks for your response.
My end date is 30 April; therefore, 1 Mar to 30 Apr should be 2.00, 1 Feb to
30 Apr should be 3.00, etc. However, 16 Mar to 30 Apr should be 1.53 or
whatever exact decimal it comes to.
I am not particular about including first and last dates, given my above
condition. All that matters is that I do not want to manually add a '+1' to
numbers.
The two decimal places are not directly for the months - i.e., 1.25 does not
mean 1 month and 25 days, but means 1 and a quarter months.
I do not use any separate equations for different dates in different months
- I just do a simple Excel calculation (date1 minus date2)/30.25
Cheers

 

[Guest]

the question on begining and end dates:
feb 20 to feb 22 is this two days or three days
what would you want the results to be between April 1 and June 1

Since no month has 30.25 days, you will always have problems with the results.
unless you make the equation much more complicated.

 

[Guest]

bj,
Thanks for your thoughts. I also tried Pete's DATEDIF solution, but that
didn't work, either. I have therefore done a multi-step workaround which
gives me what I need:
First of all, using the (date1-date)/30.25 for all 1st of the month from May
06 to April 07, I derive the exact numbers to 2 decimal places. These range
from a xx.91 to a xx.02. Then,
1. calculate the exact number as ((date1)-(date2)/30.25)
2. =if(number-(int(number))>0.91 or <0.02, round(number)
It appears there is no one-formula solution
Thanks anyway, everybody !!
Ashutosh

 

[JMay]

With A1 = 2/01/2007 and B1 = 4/30/2007
In C1 enter:
=DATEDIF(A1,B1,"m")+1

 

[Guest]

maybe something like
=if
and(day(start_date)=1,day(end_date)=eomonth(end_date)),month(end_date)-month(start_date)+1,(end_date-start_date)/30.25)

 

[Guest]

bj,
Thanks, I'll try that one...
Ashutosh