No. of days between two date in MS Excel 2003

[deepak.gupta@logica.com]

HI,

I have to find out no. of days between any two dates (may not be in the same year) with a complete accuracy, in MS Excel 2003. Is there a way to do this?


Cheers,

Deepak

 

[vijaySharma]

Deepak,


Try the below...


=CEILING((YEARFRAC(StartDate,EndDate,3)*364.25),1)


HTH


~Vijay

 

[Luke M]

Don't think so hard.

=B2-A2

or

=ABS(B2-A2)

if you don't care what order you put the dates in.


Remember, dates are stored simply as integer values in XL.

 

[vijaySharma]

Luke,


If you actually try this out... you will notice the validity of the question Deepak has put forward.


For the first go, even I was about the reply what you have done... but then I decided to check and found that it was indeed a very good question.


Deepak thanks for putting this across.


Luke... if you consider 01-Jan-2007 and 31-Dec-2008 as the dates, then going for the A1-B1 formula we will get 730 days.


Where as it should be 729 days only...


go give it a shot... my day was made by this query..


~Vijay

 

[Luke M]

Vijay,


I'm not following your example. There are 364 days remaining in the year 2007, and 365 days preceding 12/31/2008 (leap year). Following normal convention (where we include the last day, aka including the end point), this would be 364+365+1. If OP is truly defining between as exclusive of endpoints (ie, there are 2 numbers between 2 and 5) then yes, you would need to subtract 1 day, ala:

=B2-A2-1

 

[vijaySharma]

Luke,


If we need to go case by case and find the difference manually for the dates, then I may agree with what you have described above.


Can you send me your email address or drop me a mail at sharma.vijay1-at-gmail.com; I will mail the excel file i created for this and we can have a discussion on the same.


ps: I hope you have tried out the formula...


~Vijay

 

[Luke M]

Vijay,

I'm afraid I have a strict no-email policy. =(

Maybe dealing with the math itself will be less confusing. In XL, 1/1/2007 is the number 39083. 12/31/2008 is number 39813.


On a small scale, lets reduce it to 83 and 113. there are "29" numbers between these 2 if we don't include either number. However, if today is the 28, and I say I will be back in 2 days, that is commonly interpretted as meaning I'll be back on the 30th. This is what I think needs clarified.


And yes, I did try the formula. I also created the entire number line for 1/1/07 to 12/31/08, which takes 731 cells to display.

 

[deepak.gupta@logica.com]

Dear Luke, Vijay,

I must thank you for the kind help. I tried both the formulas with below start date ann end dates. Can you please confirm, which is perfect?


Start Date End Date Luke Formula Vijay Formula

------------- ------------ ------------- ------------------

2/28/2008 3/29/2011 1125 1123

2/28/2009 3/29/2011 759 758

2/28/2010 3/29/2011 394 394

2/28/2011 3/29/2011 29 29


Kind regards,

Deepak

 

[Hui]

@Deepak

=DATEDIF(A1,B1,"D")

Gives same result as Luke


So I think it depends if you include both end dates or not

A1 25/2/11

B1 29/2/11

=DATEDIF(A1,B1,"D")

=A2-A1

=4

 

[vijaySharma]

Well,


2007 has 364 days

2008 has 365 days

2009 has 364 days

2010 has 364 days.


If a simple A1-B1 is done for 1-Jan-2007 and 31-12-2007, you will get the result as 730 which is not correct


364+365 = 729 days


The original query is to find the ACTUAL difference between two dates... and that's what i have asnwered for.


@Deepak... you can try this with your real data and come back with the results.


~Vijay