Wikipedia:Reference desk/Archives/Computing/2021 April 20

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

April 20

How to format numbers in Excel to calculate time (hours/minutes)

I have Excel for Microsoft Office 365. I want to enter values such as "8:03" to represent "eight hours and three minutes". I also want to perform calculations on these time measurements: total sums, averages, etc. How can I get this format in Excel? If I go to an empty cell and type in "8:03" ... the value in that cell becomes "08:03:00 AM" (in the formula bar) ... and then it displays as "08:03" on the spreadsheet (with a leading zero). And calculations do not work as expected. I tried some of the custom formats ... but they didn't quite give me what I wanted. Also, I want a result that looks like this: "8:03" … I just want the "8:03" (with no leading or trailing zeroes) to represent a time/duration of "eight hours and three minutes". How can this be done? Thanks. Also, I don't want any indications of "seconds" or "days", etc., in the result ... I just want the "hours" and "minutes". Thanks. Joseph A. Spadaro (talk) 23:37, 20 April 2021 (UTC)

I've had a look in excel and I see a possible answer. If you enter 8:03, it will likely change this to 08:03. In the ribbon, under home is the number format drop down. Here you can select "more number formats". Under custom you have hh:mm (what it is doing now). You can change the type to [h]:mm by typing this into the field, and this should give you the desired effect. Rmvandijk (talk) 12:52, 21 April 2021 (UTC)

@Rmvandijk: Great! Thanks so much! I will try that, to see if it works! I will re-post here, once I find out. Thanks again! Joseph A. Spadaro (talk) 13:48, 21 April 2021 (UTC)

@Rmvandijk: Yes, I tried it. It works perfectly. Thank you so much. Just to satisfy my curiosity: can you explain (in simple, plain English --- not in computer jargon) ... what exactly does the format hh:mm mean ... and what exactly does the format [h]:mm mean? Thanks! Joseph A. Spadaro (talk) 14:03, 21 April 2021 (UTC)

Time fields in Excel are tricky to manage. When you enter 08:03:00 you are not entering in 8 hours and 3 minutes, you're entering in 8:03 AM. Go ahead and do it and then click back onto the cell and you can see it's changed to include the AM. So if you add 8:03 + 8:03 + 8:03, the default result will be 00:09 - meaning 9 minutes after a generic midnight, not 24:09, meaning 24 hours and 9 minutes. However, if you use the [h]:mm format, you will get the 24:09 you'd expect. But I would recommend double-checking any time-related fields and formulas thoroughly before accepting them as correct. If you add 8:03 to itself 6 times, you'll get the 48:18 you'd expect using the [h]:mm format, but if you copy and paste values you can see that Excel really thinks of it as January 2, 1900, 12:18:00 AM. This may or may not affect something downstream, so you need to be aware of it. Matt Deres (talk) 14:34, 21 April 2021 (UTC)

@Matt Deres: Thanks for the thorough explanation. Yes, it seems that Excel makes time calculations much more difficult than they need to be. You'd think that the intended meaning of "eight hours and three minutes" would be a lot more prevalent than an intended meaning of: "three minutes after eight o'clock in the morning on January 1, 1900". It is tricky, indeed. Thanks! Joseph A. Spadaro (talk) 15:05, 21 April 2021 (UTC)

@Joseph A. Spadaro: You're welcome. And I agree it's painful, though I must admit that in the 20+ years I've used Excel, I've only gotten tripped up on it a few times - the default understanding of "time stamp" versus "time increment" matches my needs. Still, it's odd that there isn't at least an alternative. The usual workaround is to express the time increments as some decimal number, so for an hour and a half you'd either enter 90 (in minutes) or 1.5 (in hours), or whatever works. If you need to add time increments to time stamps (such as adding 2 hours and 47 minutes to the time stamp of 8:07 AM on February 9), you almost certainly want to get the value into decimals of a day, since that's also how Excel does it. Since few people (except Excel people!) know that an hour is 0.041667 of a day, that would usually get calculated by a formula from something easier for people to understand. Matt Deres (talk) 19:34, 21 April 2021 (UTC)

@Joseph A. Spadaro: I think the explanation from Matt Deres is way better than any I could have given, but I am glad that my answer worked for you! Rmvandijk (talk) 08:58, 22 April 2021 (UTC)

@Matt Deres: and @Rmvandijk: ... Thanks again to you both. This perfectly solved my problems. Much appreciated. Thanks! Joseph A. Spadaro (talk) 13:47, 23 April 2021 (UTC)

Resolved