How can I convert hours in time format to days in decimal format

Question

Hello

I run a table in excel to calculate the hours I work for each client. I enter the start and end time in format hh:mm. This calculates the number of hours worked on that project each day into a total which is formatted in the cell as Time. This column is then totalled at the bottom of the table with a custom Format Cells of [h]:mm;@ because they frequently exceed 24 hours in a billing period.

How can I convert this total number of hours to a decimal number of days ie 50:00 hours into 50.0 hours so I can then calculate it on into working days (ie 7.5 hours in a working day)?

I think I am using the wrong Format Cells option but I can't work out what it should be or the formula to use.

Any help would be gratefully received!

Thank you!

Answer 1

The system stores all time values as the decimal part of a day. The format you to display this data doesn't change this fact. You may think the cell contains 8AM but if you select the cell and change the format to General, you will see the actual value 0.333333. (Even that is not the actual contents., The system stores number as floating point values in binary.)

So leave the format for the start time, end time, and duration as a convenient time format for ease of reading. In the cell where you sum the durations, change the format to General or Number with an appropriate number of decimal places. This will yield the number of 24-hour days worked. Then simply multiply by 24 to get the hours worked and divide by the number of work hours in a day to get the number of days you should be paid for.

This is my sample:

Answer 2

Hi @Sonja Porter

You may use the formula, be like =TEXT(total number,"[h]")+MINUTE(total number)/60.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---