Announcement

Collapse
No announcement yet.

Calculate Day, Months & Years Between Dates

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 19:57.

  • #2
    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, 10:26.
    .

    Comment


    • #3
      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

      Comment


      • #4
        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, 10:27.
        Kris

        ExcelFox

        Comment


        • #5
          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

          Comment

          Working...
          X