133

I'm doing some benchmarking, and I want to use Excel to produce graphs of the results. I've got a simple but annoying problem which is baking my noodle.

The problem is that Excel insists that "time" means a time of day. It refuses to let me work with time durations. When I try to say "three minutes and six seconds", it misinterprets this as "three minutes and six seconds past midnight", which isn't what I meant at all.

I can work around the problem by laboriously converting everything to seconds. But then all my graphs are labelled in seconds, not minutes and seconds. Plus it's a pain to have to keep typing in =3*60+6 rather than just 3:06. And once I've done it, I have to look at the formula to check whether I entered the data correctly [and didn't screw up the formula].

Does anybody know how I can make Excel work with a time duration not anchored to a specific time of day?

MathematicalOrchid
  • 61,854
  • 19
  • 123
  • 220
  • 2
    Excel always treats times as time of day-does it cause a specific problem? – JosieP Jun 04 '13 at 08:04
  • 4
    As there exists no Time without date for Excel just accept that there is an irrelevant date coupled to your time value. Simply format the cells in which these times are stored to reflect no date... – K_B Jun 04 '13 at 08:11
  • 1
    "I can work around the problem" - why do you need to work around this? What specifically is not as you want it right now? If it's output format, that can be controlled, see [this related question](http://stackoverflow.com/questions/6943906/timespan-in-excel-above-24-hours/6949579#6949579) – AakashM Jun 04 '13 at 08:14
  • 1
    In fact I think that might be a dupe, what say you? – AakashM Jun 04 '13 at 08:15
  • I know this is an old post, but this same issue has been bugging me for years. I've found the easiest thing to do is to just use Numbers, as it supports durations. – Glen Yates Jan 11 '22 at 18:36

9 Answers9

98

You can easily do this with the normal "Time" data type - just change the format!

Excels time/date format is simply 1.0 equals 1 full day (starting on 1/1/1900). So 36 hours would be 1.5. If you change the format to [h]:mm, you'll see 36:00.

Therefore, if you want to work with durations, you can simply use subtraction, e.g.

A1: Start:           36:00 (=1.5)
A2: End:             60:00 (=2.5) 
A3: Duration: =A2-A1 24:00 (=1.0)
Peter Albert
  • 16,917
  • 5
  • 64
  • 88
  • 11
    And a format string something like `[h] "hr" m "min" ss "s"` which will result in things like *`0 hr 28 min 48 s`* and *`32 hr 21 min 37 s`* – Ian Boyd Feb 02 '17 at 17:09
  • 7
    I had trouble using [h]:mm in Excel, it seems like the formula is localized even though I use an English version of Excel 2017 with English formulas e.g. SUM(). I live in Sweden and [t]:mm worked for me. I guess the t comes from the swedish word (t)immar instead of (h)ours. Hope this helps someone. – mikeesouth Mar 05 '18 at 14:57
  • 2
    How about total days? I want womething like 134 days, 3 hours and 4 mins. It doesn't seem to count over 31 days – Thanasis Ioannidis Jun 14 '18 at 11:49
  • 2
    @ThanasisIoannidis You're right, it can only count up to 31 as it assumes a date and then jumps to Feb 1st. You can get this with a formula: `=TEXT(A1,"0")&" day"&IF(A1>1,"s "," ")&TEXT(HOUR(A1),"0")&" hours "&MINUTE(A1)&" minutes"` – Peter Albert Jun 15 '18 at 13:36
  • 4
    The problem is I don't want a formula. I can calculate the days hours etc and produce a "string" value, but that is what it is. A string value. I want the underlying cell value to be a decimal number and only display it as days:hours:mins. I need a display format string but not a formula. – Thanasis Ioannidis Jun 18 '18 at 12:04
  • Is there a way to tell excel that the duration value is in hours, not in days? I am doing an export to excel and I can only apply format, no formula or change of value (hence cannot divide by 24). – Mohayemin May 06 '20 at 04:14
  • 1
    @Mohayemin - i feat this is not possible :-( – Peter Albert May 06 '20 at 13:02
41

Use format d "days" h:mm:ss or [h]:mm:ss, depending on your needs.

Say you have a duration of 30h 12m 54s:

  • h:mm:ss -> 6:12:54 (not correct for a duration)
  • [h]:mm:ss -> 30:12:54
  • d "days" h:mm:ss -> 1 days 6:12:54

Variations are possible: I like something like d"d" h"h" mm"m" ss"s" which formats as 1d 6h 12m 54s.

Roel Schroeven
  • 1,778
  • 11
  • 12
  • 6
    Upvote for "[h]:mm:ss -> 30:12:54". I could not figure out why "h:mm:ss" was not showing > 24 hours. – Mark Meuer Aug 17 '15 at 13:57
  • 2
    That will not work if the elapsed days is greater than 31 days, as the date will cycle to the next month and 'd' will show the date of the month, – Clement Oct 30 '20 at 20:00
19

The custom format hh:mm only shows the number of hours correctly up to 23:59, after that, you get the remainder, less full days. For example, 48 hours would be displayed as 00:00, even though the underlaying value is correct.

To correctly display duration in hours and seconds (below or beyond a full day), you should use the custom format [h]:mm;@ In this case, 48 hours would be displayed as 48:00.

Cheers.

Mike Edmonds
  • 201
  • 2
  • 2
12

With custom format of a cell you can insert a type like this: d "days", h:mm:ss, which will give you a result like 16 days, 13:56:15 in an excel-cell.

If you would like to show the duration in hours you use the following type [h]:mm:ss, which will lead to something like 397:56:15. Control check: 16 =(397 hours -13 hours)/24

enter image description here

Jochem
  • 3,295
  • 4
  • 30
  • 55
  • 2
    That will not work if the elapsed days is greater than 31 days, as the date will cycle to the next month and 'd' will show the date of the month – Clement Oct 30 '20 at 20:01
3

I don't know how to make the chart label the axis in the format "X1 min : X2 sec", but here's another way to get durations, in the format of minutes with decimals representing seconds (.5 = 30 sec, .25 = 15 sec, etc.)

Suppose in column A you have your time data, for example in cell A1 you have 12:03:06, which your 3min 6sec data misinterpreted as 3:06 past midnight, and column B is free.

In cell B1 enter this formula: =MINUTE(A1) + SECOND(A1)/60 and hit enter/return. Grab the lower right corner of cell B2 and drag as far down as the A column goes to apply the formula to all data in col A.

Last step, be sure to highlight all of column B and set it to Number format (the application of the formula may have automatically set format to Time).

tim
  • 31
  • 2
1

Highlight the cell(s)/column which you want as Duration, right click on the mouse to "Format Cells". Go to "Custom" and look for "h:mm" if you want to input duration in hour and minutes format. If you want to include seconds as well, click on "h:mm:ss". You can even add up the total duration after that.

Hope this helps.

1

The best way I found to resolve this issue was by using a combination of the above. All my cells were entered as a Custom Format to only show "HH:MM" - if I entered in "4:06" (being 4 minutes and 6 seconds) the field would show the numbers I entered correctly - but the data itself would represent HH:MM in the background.

Fortunately time is based on factors of 60 (60 seconds = 60 minutes). So 7H:15M / 60 = 7M:15S - I hope you can see where this is going. Accordingly, if I take my 4:06 and divide by 60 when working with the data (eg. to total up my total time or average time across 100 cells I would use the normal SUM or AVERAGE formulas and then divide by 60 in the formula.

Example =(SUM(A1:A5))/60. If my data was across the 5 time tracking fields was the 4:06, 3:15, 9:12, 2:54, 7:38 (representing MM:SS for us, but the data in the background is actually HH:MM) then when I work out the sum of those 5 fields are, what I want should be 27M:05S but what shows instead is 1D:03H:05M:00S. As mentioned above, 1D:3H:5M divided by 60 = 27M:5S ... which is the sum I am looking for.

Further examples of this are: =(SUM(G:G))/60 and =(AVERAGE(B2:B90)/60) and =MIN(C:C) (this is a direct check so no /60 needed here!).

Note that your "formula" or "calculation" fields (average, total time, etc) MUST have the custom format of MM:SS once you have divided by 60 as Excel's default thinking is in HH:MM (hence this issue). Your data fields where you are entering in your times should need to be changed from "General" or "Number" format to the custom format of HH:MM.

This process is still a little bit cumbersome to use - but it does mean that your data entry is still entered in very easy and is "correctly" displayed on screen as 4:06 (which most people would view as minutes:seconds when under a "Minutes" header). Generally there will only be a couple of fields needing to be used for formulas such as "best time", "average time", "total time" etc when tracking times and they will not usually be changed once the formula is entered so this will be a "one off" process - I use this for my call tracking sheet at work to track "average call", "total call time for day".

Chris
  • 19
  • 1
1

Let's say that you want to display the time elapsed between 5pm on Monday and 2:30pm the next day, Tuesday.

In cell A1 (for example), type in the date. In A2, the time. (If you type in 5 pm, including the space, it will display as 5:00 PM. If you want the day of the week to display as well, then in C3 (for example) enter the formula, =A1, then highlight the cell, go into the formatting dropdown menu, select Custom, and type in dddd.

Repeat all this in the row below.

Finally, say you want to display that duration in cell D2. Enter the formula, =(a2+b2)-(a1+b1). If you want this displayed as "22h 30m", select the cell, and in the formatting menu under Custom, type in h"h" m"m".

Martin Williams
  • 11
  • 1
0

What I wound up doing was: Put time duration in by hand, e.g. 1 min, 03 sec. Simple but effective. It seems Excel overwrote everything else, even when I used the 'custom format' given in some answers.

JosephDoggie
  • 1,514
  • 4
  • 27
  • 57