Conditional Formatting based on time difference between set criteria

[Wolfieee]

I'm trying to work out how to change the colour of a cell with conditional formatting when the time difference falls between two different ranges.

I need to calculate waiting time.

so if my time difference is works out to be 31 mins it need to change colour.

so far i have this but it doesn't work how it should

so for

31 to 45 mins =AND(MOD($AT5,1)>=31/1440,MOD($AT5,1)<=45/1440)
46 to 60 mins =AND(MOD($AT5,1)>=46/1440,MOD($AT5,1)<=60/1440)
61 to 75 mins =AND(MOD($AT5,1)>=61/1440,MOD($AT5,1)<=75/1440)
76 to 90 mins =AND(MOD($AT5,1)>=76/1440,MOD($AT5,1)<=90/1440)

Most of it works but it will not included the 1 hour time frame and only works when you get 00:59 or 01:01.

I don't know what i'm doing wrong but any help would be great.

[etaf]

seems to be working for me
upto 1:30 and then 1:31 is false

see attached formulas highlighted yellow
and conditional formatting applied to A5

[Wolfieee]

So I'm still having issues with this and can't work it out.

Anytime it get to the 61 mins it will not change with the condition formating.

Both 60 mins and 62 mins change. I don't get it. It must be an error in Excel

Does anyone know of any other formula that will allow me to do this. I've searched the internet and just can't find something that matches this.

Any help would be great

I have attached the spreadsheet so you can see what it going on. Goto Cell AT5

[AliGW]

Try this:

=AND((VALUE($AT5)*60)>=31,(VALUE($AT5)*60)<=45)

and so on.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Wolfieee]

Sorry I should have said AW5

I changed it to

Please Login or Register  to view this content.

and I couldn't get it to work. It didn't change the colours at all.

I don't know why it's not working. It should work.

[AliGW]

It works here. My guess is that another CF rule is interfering. I suggest you try it on another workbook with just that rule and see what happens.

[Wolfieee]

I've made a new workbook and still can't get it to work.

Might be something to do with the 24 hour time or the way I want it to function.

I don't know

[Phuocam]

Change to:

=AND($C1*1440>30,$C1*1440<=45)

[Wolfieee]

I changed the code and it still is doing that silly 61 min thing

I uploaded the file to see if it's just my computer or not

Edit - Reuploaded file with some changes - Thought it might be something to do with 24 hour format but it's not

[Bo_Ry]

Floating Point error
https://www.microsoft.com/en-us/micr...wrong-answers/

Please try
=AND(ROUND((C1)*1440,)>=61,ROUND((C1)*1440,)<=75)

[Phuocam]

Try again:

=AND(ROUND($C4*1440,6)>=31,ROUND($C4*1440,6)<=45)

[Wolfieee]

Please Login or Register  to view this content.

Works great !!

I would never have thought to use ROUND

Also thanks for the info aobut the Floating Point Error. Interesting that a simple calulation can cause issues.

It's been bugging me for months how to work this out.

Thanks again everyone

[Phuocam]

You're welcome! Thanks for the rep points