in Excel, i want to find the difference between two times tat cells formatted as hh:mm, ie. my times are in 24hrs format starting time is 23:30 and ending time is 02:30, actually the difference is should be 3 hrs, which formula i can apply to find the same result, pls help

Hi, If A1 contains the start time and B1 contains the end time, then enter this formula in cell C1:

=IF(B1<A1,(B1+1)-A1,B1-A1)This tests to see if the end time appears to be less than the start time, if it is, it assumes that the times are before and after midnight.

It then adds 1 to the end time before subtracting it from the start time, because 1 is Excel's value for a whole day (24 hours).

If the end time is greater than the start time it just does a simple subtraction.Note that Excel stores dates and times in the same number. Dates are the integer or whole number part of the number and the decimal part is the time.

0.5 is 12 noon, i.e., half-way through the day.When you format cells as hh:mm, there is a possibility that date information is also present, but not visible, and this can give some unexpected results.

Add data validation to the start and end cells to ensure that only times are entered. Use a start time of 00:00 and an end time of 23:59

Regards

Hi Humar

Thnk u very much for the answer i solved my problem, its working.tnx....

Hi, Glad to hear it works, and thanks for letting me know.

Regards

Humar

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History