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

Mike Bravo

New Member
Joined
Apr 8, 2008
Messages
2
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>
I was told by my manager that it was great, but was always one day short.<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Our guidelines would count the same period (1/5/08 to 1/5/08) as 1 day.
<o:p></o:p>
My question: Is there a way I can make it count inclusively, as above (1 day not 0 days)?<o:p></o:p>
<o:p></o:p>
Here's my formula, using A5 and B5 as my Start and End dates:<o:p></o:p>
=DATEDIF(A5,B5,"y") & " years, " & DATEDIF(A5,B5,"ym") & " months, " & DATEDIF(A5,B5,"md") & " days"<o:p></o:p>
<o:p></o:p>
I also have on the same sheet one counting in just days:<o:p></o:p>
=DATEDIF(A5,B5,"d") & " days"<o:p></o:p>
<o:p></o:p>
Hope someone can help, thanks in advance.<o:p></o:p>
<o:p></o:p>
~ MB.<o:p></o:p>
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

wageslave101

Board Regular
Joined
Jul 18, 2007
Messages
154
Code:
=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
 

Mike Bravo

New Member
Joined
Apr 8, 2008
Messages
2
Cheers lads, that sorted it out. I was hoping I could just put a +1 somewhere.

Thank you very much,

~ MB.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,497
Messages
5,414,891
Members
403,551
Latest member
benlinford

This Week's Hot Topics

Top