1

I am making a spreadsheet in Excel to keep track of time. When I enter 32:00:00 (hh:mm:ss) on the spreadsheet, it changes my time to 8:00:00. But when it adds up all the time at the bottom it still adds to be the right amount of days: 4 days. This is for work days taken off but I'm inputting how many hours taken off and it converts into how many days at the bottom of the worksheet.

Why does it keep changing my time?

6

Excel isn't changing the cell's value.

This is evidenced by the fact your sum operation is returning the correct result.

Instead, Excel is formatting the cell in 24-hour time notation. In this notation, 32 hours is actually "1 day and 8 hours". In this case Excel only shows the hour portion, not the days, hence the output 8:00:00. But formatting never changes the underlying data, only how it's displayed, so calculations using the cell continue to be correct.

Fortunately you can use a custom cell format to instruct Excel to show you the hours as 32:00:00. To do this:

  1. Select the cell(s) to be formatted
  2. Press Ctrl+1 to open the Format Cells dialog
  3. Select Custom in the list on the left
  4. Enter the following in the Type field:

    [h]:mm:ss
    

    enter image description here

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.