Wikipedia:Reference desk/Archives/Computing/2018 January 28

Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is an archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

January 28

Formatting time in Microsoft Excel

What is the difference if you format time in Microsoft Excel (version Microsoft Excel 2016) as h:mm versus [h]:mm? What difference do the bracket symbols (around the "h" -- and, in general) make? Thanks. 32.209.55.38 (talk) 19:16, 28 January 2018 (UTC)

According to [1] using [h] is elapsed time in hours. RudolfRed (talk) 19:29, 28 January 2018 (UTC)

OK. Thanks. But, sorry, I really have no idea what that means. I don't understand what the original source is saying. And, therefore, I really don't understand what your answer is saying. So, to rephrase my question: What is the difference when a time value is formatted as h:mm versus [h]:mm? Let's say that I enter a value of, perhaps, twelve hours and forty-eight minutes. That is, 12:48. What does "elapsed time" have to do with anything? I am confused. Thanks. 32.209.55.38 (talk) 19:50, 28 January 2018 (UTC)

Are you hung up on the word "elapsed"? That means "to pass or go by." Elapsed time is how much time has gone by. So [h] is how many hours have gone by. It is not a time you read on a clock, such as 4:29pm. It is how many hours have gone by, as you would read on a stopwatch. So, it isn't limited to 12 as with 12-hour time or 24 as with 24-hour time. It can go to 25 or 26 or 27... 71.85.51.150 (talk) 20:32, 28 January 2018 (UTC)

Suppose you enter a time equal to 100,000 seconds. It has to be entered in terms of a day, so it gets entered as 1.15740740740741. Then you try some formats. If you're in the US, and format it as a time, you get 03:46:40, that is, an entire day is just ignored. If you format it as a date, and choose a format includes the time as well, you get something like 1/1/00 3:46 (January 1, 1900, 3:46 AM). If you chose a custom format [h]:mm:00 you get 27:46:40, so the day does not just get dropped.

Which format makes more sense depends on whether you are expressing a time of day, which will never exceed 23:59:59, or the duration of an event that might exceed 24 hours. Jc3s5h (talk) 21:34, 28 January 2018 (UTC)

Thanks. But, sorry, I am now more confused than ever. This is what I want. I want to enter a bunch of times. For example: I am watching twenty-five different films. Film A lasts 1 hour and 29 minutes. (I want Excel to say "1:29".) Film "B" lasts 3 hours and 6 minutes. (I want Excel to say "3:06".) And so forth. Then, I want to add up all of the various film times. So, after I watched all twenty-five films, I have watched "54 hours and 17 minutes" of film. (I want Excel to say "54:17".) That is what I am getting at. Currently, when I do a "sum", I get an answer that will not exceed 24 hours. After the total sum hits 24 hours, it reverts back to 0:00. So, another separate example: if I watched three films at 8 hours each (8:00), and I total up that time, I should get 24 hours (or "24:00") of viewing time. But, currently, I get "00:00". Thanks. 32.209.55.38 (talk) 00:49, 29 January 2018 (UTC)

For your scenario the format should be [h]:mm Jc3s5h (talk) 01:39, 29 January 2018 (UTC)

Great! Thanks! 32.209.55.38 (talk) 15:05, 29 January 2018 (UTC)