Excel is changing my time input (32:00:00 becomes 8:00:00)

Question

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?

Answer 1

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