I then keep a running total of this time.īut another day, when I leave early because I have excess time to “use up”, the difference is negative and # results, along with the running total becoming #. If I work later than I should then I’ve worked longer & hence the difference will be positive (time worked in advance of being paid). Reason I’ve needed this is for a work timesheet. How do you calculate time difference when it’s negative, without getting the # treatment? Share your views with me in the comment section, I’d love to hear from you, and make sure to share this tip with your friends. In this formula, you have a condition if the end time is greater than the start time then it will simply deduct end time from the start time otherwise, firstly it will deduct start time from 1 and then add it into the end time.īy subtracting the start time from 1, you will get the total time for day 1 and when you add it to the end time, you’ll get the total time of day 1 and day 2 which is the total difference.įor Excel users who deal with time data, it’s really important to know various methods to get the difference between two times.Īnd all the methods which we have used above can be used in different situations and I’m sure you have found all of these helpful.ĭo you know any other method which you can use to get the difference between the start time and end time? It happens sometimes that you need to track the time difference in minutes instead of hours, and in this case, the best way is to use the below formula.ĭon’t worry you can use a better formula for this. So when you enter this formula, first of all, it returns a decimal number for the hours, and then INT removes the fractional part and gives you completed hours. INT function returns the integer part decimal by rounding it down. This formula is just like the formula which we have used in the second method, the only difference is here we have wrapped it in INT. You can also calculate the completed hours between two times with this simple formula. And when further you multiply it by 24 (total number of hours in a day) it converts that decimals into the number of hours and minutes. How this formula worksĪs I said before, Excel stores date and time in numbers, and when you deduct end time from the start time it returns a decimal. Once you enter this formula it returns the hours in numbers and minutes (if you have) in decimals. Let’s suppose you have a start and end time (like below) where you need to get the difference of both. This is one of the most common ways to get the difference between two times. So if you have a difference of 5 hours and 30 minutes it will show it as 05:00 AM. When you format that result value it shows that values as hours and minutes (starting from 00:00) instead of showing the actual difference in hours and minutes.

But there is no specific function that you can use to get the difference between times values.Īs you know, a time value is a numeric value that is formatted as time but when you deduct one value from another it returns a decimal number and you need to format it again as time. In Excel, there are a lot of functions to deal with date and time. If you work with data where you have time values then the tips which I’m going to share today can be helpful for you.

Date calculator in excel