Share via

How do I format time for DURATION.

Anonymous
2023-04-04T21:02:27+00:00

I need to construct a spreadsheet that uses the time duration that workers spend on jobs to evaluate the total times that each employee spends on different jobs. How do I format my numbers for DURATION - hours and minutes.

Also, when I add these durations, they do not need to convert to days, weeks. I want them to stay as hours because I bill in hours.. Thanks.

Microsoft 365 and Office | Excel | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2023-04-04T21:54:02+00:00

    Simply add the times worked and set the number format of the cells with the SUM formula to the custom format [h]:mm

    The [ ] around h tell Excel to display the time as duration instead of clock time.

    7 people found this answer helpful.
    0 comments
  2. Anonymous
    2023-04-04T21:52:44+00:00

    To format numbers as time durations (hours and minutes) in Excel on a PC, you can follow these steps: * Right-click and choose "Format Cells" from the context menu. Alternatively, you can go to the "Home" tab on the Excel ribbon, click on the "Number Format" drop-down arrow in the "Number" group, and then choose "More Number Formats" to open the "Format Cells" dialog box. * In the "Format Cells" dialog box, go to the "Number" tab. * Under the "Category" list, select "Time". * Choose the desired time format from the list, such as "37:30:55" for hours, minutes, and seconds or "37:30" for hours and minutes. * Click "OK" to apply the time duration formatting to the selected cells.

    If you want to ensure that the time durations remain in hours when you perform calculations such as addition, you can use the custom number format in Excel.

    Here's how you can do it: * Select the cells that contain the time duration values you want to add or perform calculations with. * Right-click and choose "Format Cells" from the context menu, or go to the "Home" tab on the Excel ribbon, click on the "Number Format" drop-down arrow in the "Number" group, and then choose "More Number Formats" to open the "Format Cells" dialog box. * In the "Format Cells" dialog box, go to the "Number" tab. * Under the "Category" list, select "Custom". * In the "Type" field, enter the following custom number format to display the time durations as hours and minutes: [hh]:mm. The square brackets around "hh" ensure that Excel treats the value as hours and does not convert it to days. * Click "OK" to apply the custom number format.

    Kindly let me know, if you require additional assistance, I will be glad to help further.

    Best Regards, Shakiru

    7 people found this answer helpful.
    0 comments
  3. Rich~M 20,370 Reputation points Volunteer Moderator
    2023-04-04T21:50:02+00:00

    Hi Charles. I am an Excel user like you.

    To show the elapsed time between two times in the same day subtract the later time from the earlier time, select the cells that you want to reflect this elapsed time, and go to the Format Cells dialogue>click on the Number tab>choose Time in the Category list>choose the selection, 13:30>click OK.

    Times that go across days, past midnight must be split to calculate the elapsed time for each day and then added together.

    The total times adding up the individual daily shifts that go over 24 hours total must be formatted under Custom as [h]:mm for it to give a total over 24 hours. Use the square brackets around the letter h.

    If this is not already in the Custom list, add it in the Type line circled in red above and then click OK.

    Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

    Rich~M

    5 people found this answer helpful.
    0 comments