Split total time worked into Hours, Minutes, and Seconds Often, the cell will have some sort of date format by default and won't output the correct value until you change the formatting you may think that you did something wrong when, in fact, it's just the formatting of the cell. You could also just multiply it by 86400 (total seconds in a day), but it is often easier to remember 24 *60*60 since that is just the number of hours in the day multiplied by the number of minutes in an hour multiplied by the number of seconds in a minute. Since Excel stores time as a fraction of a day, this is why we can multiply it by 24*60*60 to get the correct result. *24*60*60 represents the number of seconds in a day hours in a day * minutes in an hour * seconds in a minute. INT() rounds the number down to the nearest integer and so the decimal amount is simply removed.

This returns full minutes and removes any stray seconds from the end. You could also just multiply it by 1440 (total minutes in a day), but it is often easier to remember 24 *60 since that is the number of hours in the day multiplied by the number of minutes in an hour. Since Excel stores time as a fraction of a day, this is why we can multiply it by 24*60 to get the correct result. *24*60 represents the number of minutes in the day hours in a day * minutes in an hour. Make sure the cell that outputs the new number is formatted as General. Home tab > Number > General or Right-click the cell > Format Cells > Number tab > General. This example is also in the last section of this tutorial. You can test this by entering this number into a cell: 27:15:30 and formatting it like :mm:ss and then using the HOUR() function to try to get the total hours. If you, for instance, store a time that has 27 for the hour, the HOUR() function will return 3 instead of 27. The HOUR() function will return the hour number from a time, however, it only works for times that are under 24 hours. INT() rounds the number down to the nearest integer, so the decimal amount is simply removed. This returns full hours and removes any stray minutes from the end. Since Excel stores time as a fraction of a day, this is why we can multiply it by 24 to get the correct result. *24 represents the number of hours in the day. This method changes the internal format of the number so that it will no longer be in the time-serial format in Excel - this just means that Excel won't see it as a date anymore. This format allows you to use the time to calculate wages, sum total time worked, and more, without any formatting confusion. Easily convert time to decimal format so that 9:15 AM or 9:15 will become 9.25, which means 9 hours and.

Converting clock time to decimal