Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Calculate Day, Months & Years Between Dates

1. Senior Member
Join Date
3rd October 2006
Location
Cardiff, UK
Posts
263

## Calculate Day, Months & Years Between Dates

Hi,

I have two columns with dates (and times) in that I am trying to define how many days, hrs and mins have elapsed i.e. A1 has 12/12/06 21:00, B1 has 17/12/06 21:00. C1 has B1-A1 and is custom formatted to show as dd"days" hh"hrs" mm"mins". In this case it will therefore show as 5days 0hrs 0mins. Which is correct.

However, if more than 1month has elapsed then the format m"m" d"days" h"hrs" m"mins" does not work. For example 17/03/06 03:00 to 20/12/06 07:00 shows as 10m 4days 4hrs 00min, which it clearly isn't.

I know the reason it does this is because it calculates the difference between the two times and adds that to it's 0 value, which in my format is 01/01/1900 00:00. therefore when it adds 277days (the answer) it becomes 04/10/1900 04:00, so my formatting is just calling the month value ('10') and the day value ('4').

I understand the reason it does this, 277 days on from 01/01/1900 is indeed Oct 4th, but 277 days on from 17/03/06 is not 10months and 4 days as there are different length months in between. It also seems to add a month on, possibly because the format for 'months' is between 1 & 12 and therefore cannot begin at 0?

Does anyone know if it's possible to force excel to work out the correct number of months and days have elapsed between two dates and not apply it to 01/01/1900? Or any other possible solution, maybe with a different custom format?

Thanks in advance for any help/suggestions.

KJ
Last edited by Kneejerk; December 20th, 2006 at 19:57.

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Correct Format For Time Between Two Dates

Try changing the Custom Formats so the dd part is [dd]

Last edited by Dave Hawley; December 21st, 2006 at 10:26.

Excel Video Tutorials / Excel Dashboards Reports

3. Senior Member
Join Date
3rd October 2006
Location
Cardiff, UK
Posts
263

## Re: Correct Format For Time Between Two Dates

I did try [d] but it is not recognised in the way that [h] is. [dd] doesn't work either

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: Correct Format For Time Between Two Dates

Hi,

Try,

=DATEDIF(A1,B1,"ym")&" Month(s) "&DATEDIF(A1,B1,"md")&" Day(s) "&TEXT(B1-A1+(B1>A1),"hh")&" Hour(s) "&TEXT(B1-A1+(B1>A1),"mm:ss")&" Min(s) "

Also See Calculate a Person's Age in Excel
Last edited by Dave Hawley; December 21st, 2006 at 10:27.

5. Senior Member
Join Date
3rd October 2006
Location
Cardiff, UK
Posts
263

## Re: Correct Format For Time Between Two Dates

Wow! I wouldn't have been able to come up with that answer as long as I lived, but it works.

Thank you very much. Once again proving how valuable this resource is.

KJ

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno