Difference between two times

de11

New Member
Joined
Nov 1, 2007
Messages
16
I am using the following formula to calculate the difference between two times :

if(K5>=K4,K5-K4,K4-K5)

however when K5 is less than K4 formula returns #############

Not sure what I am doing wrong had it working earlier, unless a format has now changed??
The times never cross over 24hrs.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
That means a negative date / time value, although your formula should allow for that.

Try

=ABS(K5-K4)

as an alternative, see if that works or gives the same ######## result
 
Upvote 0
Thanks that has provided the difference.

If I wanted to determine which of the two times were greater how can I do this if I have a negative date/time value?
 
Upvote 0
Sorry, I'm not sure if I'm following that correctly,

Are you looking for something to say, for example, "K5 is greater" ?
 
Upvote 0
I have two times K4 = 23:04:59 (which based on your last answer maybe the negative date/time. K5 = 23:00

=if(K4>=K5,"Okay",ABS(K5-K4)), I was expecting this to return "Okay" but I get the time difference. Which is an improvement as before I had ############
 
Upvote 0
That should return "Okay", if the time in K4 is negative then you should see it prefixed with - or maybe in red text depending on your format settings.

What result do you get from =TEXT(ABS(K4-K5),"[h]:mm:ss")
 
Upvote 0
I am currently getting the difference in minutes returned.

However when the argument is met I want it to return "Okay" but still get the difference.

Checked on negative time and changed to 1904 date system but no improvement.
One of my times is a date/time and the other just a time.
 
Upvote 0
One of my times is a date/time and the other just a time

That makes a big difference, date and time values are based on decimals, date to the left of the decimal point, time to the right.

A time with no date will have a decimal value between 0 and 1, current dates have a value of close to 41,000 so when there is a date and time it will always be much greater.

To get a true result you need to isolate the time value from the cell with the date using a formula like

=IF(MOD(K4,1)>=MOD(K5,1),"Okay","Not Okay")
 
Upvote 0

Forum statistics

Threads
1,203,096
Messages
6,053,510
Members
444,669
Latest member
Renarian

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top