Work with DURATION time, not time of day

Question

I am benchmarking and I want to use Excel to create graphs of results. I have a simple but annoying problem that bakes my noodles.

The problem is that Excel insists that "time" means the time of day. He refuses to let me work with time. When I try to say “three minutes and six seconds,” he misinterprets it as “three minutes and six seconds past midnight,” which is not at all what I had in mind.

I can get around the problem by painstakingly converting everything into seconds. But then all my charts are marked as seconds, not minutes and seconds. In addition, the pain should continue to print at =3*60+6 , not just 3:06 . And once I have done this, I have to look at the formula to check if I entered the data correctly [and did not ruin the formula].

Does anyone know how I can get Excel to work with time not tied to a specific time of day?

Answer 1

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

This means that the time / date format is 1.0, equal to 1 full day (starting from 1/1/1900). Thus, 36 hours will be 1.5. If you change the format to [h]:mm , you will see 36:00 .

Therefore, if you want to work with duration, you can simply use subtraction, for example.

 A1: Start: 36:00 (=1.5) A2: End: 60:00 (=2.5) A3: Duration: =A2-A1 24:00 (=1.0) 

Answer 2

Use the d "days" h:mm:ss 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 (does not correspond to the duration)
• [h]:mm:ss → 30:12:54
• d "days" h:mm:ss → 1 day 6:12:54

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

Answer 3

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

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

Answer 4

I don’t know how to make a chart an axis designation in the format “X1 min: X2 sec”, but here is another way to get the duration in minutes format with decimal places representing seconds (.5 = 30 seconds, .25 = 15 seconds, etc. .)

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

In cell B1, enter this formula: = MINUTE (A1) + SECOND (A1) / 60 and press enter / return. Grab the bottom right corner of cell B2 and drag until column A applies the formula to all the data in column A.

Last step, be sure to select the entire column B and set it to the number format (the formula application can automatically set the format to Time).

Answer 5

Custom format hh: mm shows only the number of hours until 23:59, after which you get the balance, less than full days. For example, 48 hours will be displayed as 00:00, although the lining value is correct.

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

Greetings.

Answer 6

The best way to find this problem is to use a combination of the above. All my cells were entered as a user format to display only "HH: MM" - if I entered "4:06" (4 minutes and 6 seconds), the field will display the numbers I entered correctly, but the data itself will represent HH: MM in the background.

Fortunately, time is based on 60 ratios (60 seconds = 60 minutes). So, 7H: 15M / 60 = 7M: 15S - I hope you see where it goes. Accordingly, if I take my 4:06 and divide by 60 when working with data (for example, to sum the total time or average time by 100 cells, I would use the usual AMOUNT or MEDIUM formulas, and then divide by 60 in the formula.

Example = (SUM (A1: A5)) / 60. If my data was within 5 temporary tracking fields, it was 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 these 5 fields, what I want should be 27M: 05S, but instead 1D: 03H: 05M: 00S is shown. As mentioned above, 1D: 3H: 5M divided by 60 = 27M: 5S ... which is the amount I'm looking for.

Other examples of this are: = (SUM (G: G)) / 60 and = (MEDIUM (B2: B90) / 60) and = MIN (C: C) (this is a direct check, so this is not necessary!).

Please note that the “formula” or “calculation” fields (average, total time, etc.) MUST have a custom MM: SS format when you split by 60, since Excel's default thinking is in HH: MM (hence this problem). The data fields in which you enter your time must be changed from the "General" or "Number" format to the custom format HH: MM.

This process is still a bit cumbersome to use - but it means that your data entry is still very simple and “correctly” displayed on the screen as 4:06 (which most people will consider as minutes: seconds when under the heading " Minutes "). Usually, only a few fields are required during tracking, which must be used for such formulas as "best time", "average time", "total time", etc., and usually they will not be changed after entering the formula, be "one off "process - I use this for my call tracking table at work to track" average call "," total call time for the day. "

Answer 7

Let's say that you want to display the time elapsed between 17:00 on Monday and 14:30 the next day on Tuesday.

In cell A1 (for example), enter the date. In A2, time. (If you enter 5 pm, including a space, it will be displayed as 5:00 pm. If you want the day of the week to be displayed as well, then in C3 (for example) enter the formula = A1, then select the cell, go to the formatting drop-down menu , select "Custom" and enter dddd.

Repeat all this in the line below.

Finally, let's say you want to display this duration in cell D2. Enter the formula: = (a2 + b2) - (a1 + b1). If you want this to appear as "22h 30m", select a cell and in the formatting menu under "Custom" enter h "h" m "m".

Answer 8

Select the cell (s) / column that you want as "Duration", right-click on "Format Cells". Go to "Custom" and look at "h: mm" if you want to enter the duration in hours and minutes. If you want to turn on and seconds, press "h: mm: ss". You can even add the total duration after that.

Hope this helps.

Answer 9

What I finished was: to put the length of time manually, for example, 1 min, 03 sec. Simple but effective. Excel seems to have rewritten everything else, even when I used the "custom format" specified in some answers.