Time Formula Issue

[Arts]

Hi all

I have the following formula I am using to get the difference in mins. For what ever reason the time shows up as 10:23:33:00 from a separate system when loaded into Excel so am using the following formula =TEXT(LEFT(BG6,5)-LEFT(BF6,5),"h:mm") for the most part which works.

Now the issue I am having is how to include in an IF statement if the mins difference is more than 15 mins I need it to show error.

My first result is showing 0:05 (so 5 mins difference) but the issue is how to show this in an IF as =IF(TEXT(LEFT(BG6,5)-LEFT(BF6,5),"h:mm")>15,"Error","ok") shows error when it shouldn't. Have tried a few various ways to show this but don't seem to be having much luck. Any help would be appreciated.

Thanks
Arts

 

[Weazel]

you can try something like....

=IF(0+TEXT(LEFT(BG6,5)-LEFT(BF6,5),"h:mm")>TIME(0,15,0),"error","ok")

The 0+ converts the text time into a real time, then you can compare it in the time function.

 

[Arts]

Weazel

Thanks for this!! Works perfectly, could I ask what the difference is between text time and real time?

Also this maybe an easier fix as people may play around with the formula. How would I get this to work with conditional formatting? So what formula what I enter to format the cell in red if the time shows more than 0:15. I have tried to enter if BH6>0:15 but it doesn't accept the ":"

 

[Weazel]

Ok, text time is just a time value formatted as text, in this case, the TEXT and LEFT extract the time value as text, it looks like time but is actually text, usually justified to the left in the cell.
Excel is not able to do time calcultions on text so if you try to subtract the returned values you will get a #value error. Performing a math operation on the text time will convert it to an actual excel time that you can add, subtract, etc.

for the conditional formatting you could try =BH6>"0:15:00"+0 , again, the +0 is converting the text 15 mins to actual time 15 mins.

it may be easier to just use the TIME function BH6>TIME(0,15,0), its just a little more straightforward IMO

 

[Arts]

That was going to be my next suggestion, if it would make sense to just use the TIME function by itself rather than what I had attempted with the LEFT etc but I did this initially as the time is being shown as 10:23:33:00 but either way you've provide me with the solution so thanks once again for this and the explanation.

Have just tried the =BH6>"0:15:00"+0 with the conditional formatting and doesn't seem to work. Has highlighted all values.

Arts

 

[Arts]

Hi guys

I had a few goes at this on the weekend but no luck, if anyone could assist how I could apply conditional formatting, anything over 15 mins highlighted as red. The value is shown as "0:05". Previous suggestion of =BH6>"0:15:00"+0 doesn't seem to work.

Thanks