DATEDIF fuction in XL03 on WindowsXP - How to count Inclusive of start and end dates?

[Mike Bravo]

Hoping someone can help me out. I work in employment services, and we often need to figure out how long a client spent working with a certain employer, in terms of how many days. We need to figure out the total time they’ve spent working, despite the gaps between employment periods.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I knocked up a spreadsheet using the datedif function, letting me put in two dates (the date they started and finished with a certain employer), to give me an answer.<o></o>
I was told by my manager that it was great, but was always one day short.<o></o>
<o></o>
For our purposes, we must count inclusive of the start and end dates - I can't get excel to do this. Eg. it will count 1/5/08 to 1/5/08 as 0 days.<o></o>
<o></o>
Our guidelines would count the same period (1/5/08 to 1/5/08) as 1 day.
<o></o>
My question: Is there a way I can make it count inclusively, as above (1 day not 0 days)?<o></o>
<o></o>
Here's my formula, using A5 and B5 as my Start and End dates:<o></o>
=DATEDIF(A5,B5,"y") & " years, " & DATEDIF(A5,B5,"ym") & " months, " & DATEDIF(A5,B5,"md") & " days"<o></o>
<o></o>
I also have on the same sheet one counting in just days:<o></o>
=DATEDIF(A5,B5,"d") & " days"<o></o>
<o></o>
Hope someone can help, thanks in advance.<o></o>
<o></o>
~ MB.<o></o>

 

[wageslave101]

=DATEDIF(A5,B5,"y") & " years, " & DATEDIF(A5,B5,"ym") & " months, " & DATEDIF(A5,B5,"md")+1 & " days"

=DATEDIF(A5,B5,"d")+1 & " days"

Should ensure the inclusive day - I tested a variety and it seems to work fine.

Cheers,
WageSlave

 

[dafan]

Alternatively you could try A5-1 :P

 

[Mike Bravo]

Cheers lads, that sorted it out. I was hoping I could just put a +1 somewhere.

Thank you very much,

~ MB.