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

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