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

[wrecclesham]

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?

 

[wrecclesham]

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

 

[SpillerBD]

=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]

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]

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.

 

[joeu2004]

=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]

Change the custom format to: # "days"

 

[wrecclesham]

@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!!!