CODEWITHSUNDEEP
exceltimenumber-formatting
user2348797
user2348797

Reputation: 383

Better Way to Format Time as Days, Hours, Minutes, and Seconds in Excel?

In Excel, I usually keep track of time taken by putting hours, minutes, and seconds into separate cells. I find that Excels built in date and time format has many quirks when it comes to adding and subtracting, which is why I avoid it.

Right now I have an Excel sheet keeping time of a repeated task like this:

|  H  |    I    |

| Min | Seconds |  
|  7  |   15    |
|  7  |   51    |
|  8  |   05    |

In cell E5 I then put the total time, of the tasks, in seconds and convert it into days

=(SUM(H:H)*60+SUM(I:I))/60/60/24

Now I want the total from E5 to be displayed in days, hours, minutes, and seconds. I could format E5 as d" days "h" hours "mm" min "s" s", but this does not work if the total time exceeds 30 days.

So what I have right now, in a different cell, is this formula:

=TRUNC(E5)&" days "
&TRUNC((E5-TRUNC(E5))*24)&" hours "
&TRUNC(((E5-TRUNC(E5))*24-TRUNC((E5-TRUNC(E5))*24))*60)& " min"
&TRUNC(((((E5-TRUNC(K5))*24-TRUNC((E5-TRUNC(E5))*24))*60)-
TRUNC((((E5-TRUNC(E5))*24-TRUNC((E5-TRUNC(E5))*24))*60)))*60)&" s"

(Note: The forumal is all on one line in Excel, I formatted it here so it's easier to read.)

The formula I have works and is accurate with any amount of time I use, but it's long and hard to follow. Is there any better way to do this?

(Keep in mind that I am working with cells formatted as numbers. There are not formatted as dates and they are not formatted as times.)

Upvotes: 2

Views: 7408

Answers (1)

user4039065
user4039065

Reputation:

As you can see in the below image, I could not get your provided formula working (in E6) to any degree of certainty but I devised a formula coupling some worksheet functions with custom formatting masks that seems to work well.

      TRUNC days from datetime

The formula in E7 is,

 =INT(E5)&TEXT(MOD(E5, 1), " \d\a\y\s h \h\r\s m \mi\n\s s \s\ec\s")

You cannot use both 0 and h in the same format string but you can remove the day portion and format for h, m and s. The backslashes in the format mask are escape characters and negate the need to double up quotes within a quoted string. The INT function resolved the integer portion and the MOD function provides the decimal (aka Time) portion.

¹ I prefer the INT function over the TRUNC function for removing the decimal portion of a number. This is strictly personal preference. In this case there is no difference. Even ROUND could be used.

Upvotes: 3

Related Questions