In order to calculate how much time has elapsed since the start time to now, you simply use the NOW function to return today's date and the current time, and then subtract the start date and time from it. Calculate elapsed time from a start time to now the result of the formula is a negative number), the #NUM! error is returned. If the end time is less than the start time (i.e. The elapsed time is displayed differently depending on the time format applied to the column:

With Start times residing in column A and End times in column B, you can copy the following formula in columns C though E: Please see Creating a custom time format in Excel for the detailed steps.Īnd now, let's see how our time difference formula and time codes work in real worksheets. To apply the custom time format, click Ctrl + 1 to open the Format Cells dialog, select Custom from the Category list and type the time codes in the Type box. To make them more informative, you can apply custom time formatting with one of the following codes: Time codeĮlapsed hours and minutes, display as 4:10.Įlapsed hours, minutes and seconds, display as 4:10:20. The decimals in column D are perfectly true but not very meaningful. Remembering that in the internal Excel system, times are represented by fractional parts of decimal numbers, you are likely to get the results similar to this: =TIME(HOUR(A2), MINUTE(A2), SECOND(A2)) - TIME(HOUR(B2), MINUTE(B2), SECOND(B2))Ĭalculates the time difference between values in cells A2 and B2 ignoring the date difference, when the cells contain both the date and time values. =TIMEVALUE('8:30 PM') - TIMEVALUE('6:40 AM')Ĭalculates the difference between the specified times. In your example, you're finding the difference between 12:00 and 12:30 so you should actually be getting a result of 2.08333333333333E-02 which if multiplied by 1440 would give you 30mins.Įxcel gives you tools to find the difference between two points in time though that take all that complex math(s) away - DateDiff and has given you everything you need there to write your own code.Depending on you data structure, the actual time difference formula may take various shapes, for example: FormulaĬalculates the difference between the time values in cells A2 and B2. So the result itself represents the decimal fraction of an entire day, if you want this in minutes for example, you would multiply the value by 1440 (24hr x 60min make a day) which would give you 31mins. Or you could think of the E-02 part as meaning shift the decimal point two places to the left (left because it's negative).Įxcel treats time (both dates and times of day) as whole days so half a day (12 hours) would be represented as 0.5. In your case, it means 2.1527777777778 times 10 to the power of -2. 2.1527777777778E-02 is using a method of displaying a value called Scientific Notation.

How to subtract times in excel formula