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

wrecclesham

New Member
Joined
Jul 24, 2019
Messages
45
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

<code><code>=A1-B1</code></code>

The cell displaying the difference has the following custom formatting:

<code><code>d "days" h "hours" m "minutes"</code></code>

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:

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,688
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
 

wrecclesham

New Member
Joined
Jul 24, 2019
Messages
45
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.
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,541
Office Version
2010
Platform
Windows
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:

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,541
Office Version
2010
Platform
Windows
=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.
 

wrecclesham

New Member
Joined
Jul 24, 2019
Messages
45
@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:

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

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!!! :)
 

Forum statistics

Threads
1,078,464
Messages
5,340,460
Members
399,376
Latest member
Tresfjording

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top