Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Calculate Day, Months & Years Between Dates

  1. #1
    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. #2
    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]

    See Also Excel Dates & Times
    Last edited by Dave Hawley; December 21st, 2006 at 10:26.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    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. #4
    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. #5
    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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Add Months & Return Decimal as Years & Months
    By kuj in forum Excel General
    Replies: 12
    Last Post: February 19th, 2013, 16:47
  2. Calculate Months Between Dates
    By jry in forum Excel General
    Replies: 8
    Last Post: August 22nd, 2007, 10:10
  3. Years & Months Between Two Dates
    By mgiv in forum Excel General
    Replies: 2
    Last Post: April 12th, 2007, 13:58
  4. Calculate Age In Years And Months
    By RichardS in forum Excel General
    Replies: 4
    Last Post: October 5th, 2006, 20:22

Bookmarks

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