In tables working with time values is sometimes necessary to calculate a period longer than 24 hours. If the result is not format correctly, you will not get the desired value, because by default, Excel does not calculate the time exceeding 24 hours. But just change the custom cell format and the correct number appears. See instructions below. If this guide has helped you, become a fan on Facebook and recommend this site to your friends, it can be useful for them too.

More than 24 hours

In a picture below we demonstrate the value of the arrival and departure of a worker Jarda. The cells contain day and time values. In this case, the poor Jarda, according to data, is working for 25 hours. We cannot find out whether he forgot to write down the correct time or asleep at work. But we want to calculate the exact time between the two values.

Rozdíl hodin

Rozdíl hodin

Ordinary, subtract those cells get the number of days, a format of decimal number. Time format would show us only 1 hour instead of 25 hours, because Excel does not consider the difference in time more than 24 hours, which is the length of one day. Therefore, change the number format to [h]: mm as shown in the next image. Right-click the cell E2, choose Format Cells > Number > Custom.

Formát 24 hodin

Formát 24 hodin

Leave a Reply

Your email address will not be published. Required fields are marked *