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

#### wrecclesham

##### New Member
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:

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### wrecclesham

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

#### SpillerBD

##### Well-known Member
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
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
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
=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.

#### Kenneth Hobson

##### Well-known Member
Change the custom format to: # "days"

#### wrecclesham

##### New Member
@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!!!