The start and stop times were entered with AM and PM formats. In this example I wanted to know the elapsed time between the first and second values:Īs you can see I wanted to calculate how much time elapsed between 8:30 AM and 10:30 PM by subtracting the 8:30 start time value in cell A2 from the 10:30 PM end time in cell B2. Understanding how Excel looks at dates and times can help us to demystify time calculations.

Ap1:39 pm is represented by the number 39916.56875. If you enter the current date and time in a cell and set the format to General or Number, you will see the date’s serial number version. This format is known as “serial date” or “serial date-time”.

They are instead numbers stored in a dddd.tttt format, where the digits to the left of the decimal point tell the number of days since January 1900 and the decimal tells the part of the day that has passed since midnight. Excel refers to these as serial numbers, but they are not serial numbers as you may be used to seeing them as a mix of letters and numbers. Excel would be a good tool for these kinds of tasks.Įxcel’s ability to perform complex calculations is well known, but asking it to return simple time calculations can produce confusing results unless you understand all the factors involved.Īt the heart of date/time values in Excel is the fact that they are stored as serial numbers that indicate days and decimals of days since the beginning of January, 1900. Or, in planning for a lawn care business, you want to track times required to mow lawns of various sizes.

Let’s say that to make a point about the relationship between hot water usage and the size of the utility bill, you want to track how long your teenager spends in the shower.

Time calculation formula for excel