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,694
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,580
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,580
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,081,936
Messages
5,362,249
Members
400,672
Latest member
ExcelGrasshopper

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top