Results 1 to 8 of 8

Thread: Displayed date difference starts counting from one day again whenever a date difference exceeds one month
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Location
    Wrecclesham, Farnham, UK
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Displayed date difference starts counting from one day again whenever a date difference exceeds one month

    I'm trying to calculate the date/time difference between two cells, to be displayed in days, hours and minutes.

    Right now, if I simply use

    =A1-B1

    The cell displaying the difference has the following custom formatting:

    d "days" h "hours" m "minutes"

    The problem I'm having is that, as soon as the difference exceeds 32 days, the difference is displayed as 1 day and starts counting back up again from there.

    How can I get a displayed date difference to show a result in days that is able to exceed 31 days?

    I don't want to display the extra difference in months or years. The number of days should simply be able to continue to increase beyond 32 days.

    Any ideas?
    Last edited by wrecclesham; Sep 8th, 2019 at 10:47 AM.

  2. #2
    New Member
    Join Date
    Jul 2019
    Location
    Wrecclesham, Farnham, UK
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Displayed date difference starts counting from one day again whenever a date difference exceeds one month

    This is probably a simple issue but I can't quite figure it out!

  3. #3
    Board Regular
    Join Date
    Jul 2014
    Location
    Memphis, TN
    Posts
    2,680
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Displayed date difference starts counting from one day again whenever a date difference exceeds one month

    Code:
    =TEXT(INT(A1-B1),"0")&" Days "&TEXT(MOD(A1-B1,1),"H")&" Hours "&TEXT(MOD(A1-B1,1),"m")&" Minutes"
    This is not a value but a text result
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

  4. #4
    New Member
    Join Date
    Jul 2019
    Location
    Wrecclesham, Farnham, UK
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Displayed date difference starts counting from one day again whenever a date difference exceeds one month

    Thanks! I'll give that a try.

    If anyone can figure out a way to do this and return the result as a value, that would be awesome.

  5. #5
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,487
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Displayed date difference starts counting from one day again whenever a date difference exceeds one month

    Quote Originally Posted by wrecclesham View Post
    If anyone can figure out a way to do this and return the result as a value, that would be awesome.
    Well, a string is a "value". Presumably, you mean a numeric value, using cell formatting to display days.

    It cannot be done!

    You need to use two cells:

    1. One cell with the numeric result of =A1-B1 formatted as General or Number; and

    2. One with (simply) =INT(A1-B1) & TEXT(A1-B1, " ""days"" h ""hours"" m") & " minutes"

    Note the use of a pair of double-quotes within the TEXT second parameter.

    And of course, you can replace A1-B1 with a reference to the numeric value in the first cell (#1).

    -----

    Your original format does not work because "d" formats the day of the month, not the number of days.

    It starts over at 1 after 31 days because 32 is interpreted as 1 Feb 1900.

    Recall that dates are stored as number of days since 31 Dec 1899. Thus, 1 is 1 Jan 1900; 2 is 2 Jan 1900; and 31 is 31 Jan 1900.

    So when the difference in days is zero to 31, "d" displays what you expect only by coincidence.
    Last edited by joeu2004; Sep 8th, 2019 at 09:01 PM.

  6. #6
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,487
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Displayed date difference starts counting from one day again whenever a date difference exceeds one month

    Quote Originally Posted by joeu2004 View Post
    =INT(A1-B1) & TEXT(A1-B1, " ""days"" h ""hours"" m") & " minutes"
    Alternatively (with =A1-B1 in C1):

    =INT(C1) & " days " & HOUR(C1) & " hours " & MINUTE(C1) & " minutes"

    That has an advantage of being able to customize the plurals, to wit:

    =INT(C1) & IF(INT(C1)=1, " day ", " days ") & HOUR(C1) & IF(HOUR(C1)=1, " hour ", " hours ")
    & MINUTE(C1) & IF(MINUTE(C1)=1, " minute", " minutes")

    In any case, it is not necessary to write MOD(C1,1) in order to separate the time part from the date part.

  7. #7
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,079
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Displayed date difference starts counting from one day again whenever a date difference exceeds one month

    Change the custom format to: # "days"

  8. #8
    New Member
    Join Date
    Jul 2019
    Location
    Wrecclesham, Farnham, UK
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Displayed date difference starts counting from one day again whenever a date difference exceeds one month

    @joeu2004

    That works perfectly!!

    I was actually wondering if I could somehow customize the plurals but thought it might be too difficult.

    I've adapted it slightly. Originally, I used a hidden helper column for (A1-B1) but then realized I could include it in my C column formula and do away with the helper column altogether:

    =IF(ISBLANK(A1),"",(INT(A1-B1) & IF(INT(A1-B1)=1, " day ", " days ") & HOUR(A1-B1) & IF(HOUR(A1-B1)=1, " hour ", " hours ")))

    I also decided that I don't want the minutes displayed after all, so I removed that part.

    The plural customization is a very nice touch.

    THANKS!!!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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