Why does SUM not work when adding hours:mins

[Johfra]

Hello,

Attached you will find the spreadsheet I created to count the hours that my staff nurse works.

I entered the minutes (J-U) she's worked and added a calculation column (X-AI) where it would convert the entries into actual h:mm (I used an "IF" formula - also clearly viewed in the attachment).

I want to sum the hours per month (column) BUT, it won't let me. I've been researching online, I've followed all advises, I've customized each cell for h:mm & for the total its [h]:mm. I've also customized for all to be [h]:mm and simply h:mm. NOTHING works.

As you can see, the total per row works! But, not when I want to tally up the column.........I'm stumped.

Can anybody help me out?

[CK76]

In Excel, time values are stored as decimal (i.e. fraction of day).
1 = 1 day or 24 hours
1/24 = 1 hour or 60 min
1/24/60 = 1 min or 60 seconds

So if you want to convert minute value stored in J to U. Instead of using long IF formula. Just divide the cell value by 1440. Format cells as [h]:mm and Sum values.

But hard to say, exactly what's causing your issue without the actual file. You can desensitize the data, while keeping structure same to demonstrate your issue.

Edit: To upload file, use "Go Advanced" button, then find and click on "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.

[José Augusto]

hi

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Tsjallie]

Assuming that row4-6 contain the names of the nurses.
Enter this formula into cell V48:

Please Login or Register  to view this content.

Format the cell in columns V:AE as Time.
In column AH you can just sum columns V:AE

[Johfra]

Added excel file

[CK76]

Here. See attached.

As I stated. In X7:
=J7/1440
Copy across and down. Format Total row as [h]:mm

Note: I only changed first few.

[Johfra]

Thank you all for the amazing help!! CK76 - this is amazing! I can't thank you enough! I never heard of this method/trick. I searched online for days (not full days but, on and off) and I never came across this. Thanks again so much for your help - really appreciate the time you took to answer.

[CK76]

You are welcome.

You may want to read link below. Most programs (including Excel). Stores date/time value using some serial number (Ex: Unix Epoch time, stores value in seconds from 0:00 UTC 1/1/1970).

https://www.myonlinetraininghub.com/excel-date-and-time