IF function not respecting conditions....

[DawsonG]

Hello everyone,

So basically I have a sheet with a time table of Monday to Friday for daycare employees to insert their schedule. These employees do not necessarily work 7hrs a day in a block, they can work in the AM and return after noon, if there is a gap of 1.5 hrs or more they receive a split shift premium. There are two formulas of concern. The first calculates the split between shifts, based on selections from drop down lists, =IF(E17=$Y$8,0,(C18-E17)*24), this one works fine.

The second formula looks at the results of the first and determines if there is a gap >=1.5 =IF(OR(AA16>=1.5,AA17>=1.5,AA18>=1.5),"Y","N").

The problem is with the second formula. AA16 is equal to 1.5 (not 1.49999 etc) yet returns a "N". If I change the reference to another cell and just type 1.5 if says "Y".

Why is the second IF function not respecting the condition? It only seems to mess up if the first one is exactly 1.5 despite the condition being >=

If anyone has an idea on the problem it would be greatly appreciated! Thanks!

-Dawson

[Pete_UK]

Times in Excel are stored internally as fractions of a 24-hour day, and there can be a difference between the decimal fractional values that we use and the binary representation of those fractions which computers use. These differences (often very small values) can be hidden by formatting and if you have a value like 1.499999999999 which is formatted to show 1 decimal place it will look like 1.5. One way around it is to wrap a rounding function around your cells, like this:

=IF(OR(ROUND(AA16,1)>=1.5,ROUND(AA17,1)>=1.5,ROUND(AA18,1)>=1.5),"Y","N").

Or you can put the ROUND functions within the cells AA16 to AA18.

Hope this helps.

Pete

[Kaper]

if you need a referenco of more or equal than 1.5 hours it will be safer to check if it is more than 1hr29min59.5s which seems to be crazy, but due to limited precision of floating point calculations is sometimes needed.

so try

Please Login or Register  to view this content.

etc

[Kaper]

PS. instead of OR if you want to compare several values against one, you can use MAX of the values to be compared, so either:
=if(max(AA16,AA17,AA18)>=5399.5/86400,"Y","N")

@Pete_UK: as for rounding - I'd not go for round(somehourswithdecimalpart,1), because one will get 1.5 for any time between 01:27:00 and 01:33:00
if rounding, then even ,3) is not enough 01:29:00 rounds to 1.500 but ,4) will be fine :-)

[Pete_UK]

@Pete_UK: as for rounding - I'd not go for round(somehourswithdecimalpart,1), because one will get 1.5 for any time between 01:27:00 and 01:33:00
if rounding, then even ,3) is not enough 01:29:00 rounds to 1.500 but ,4) will be fine :-)

Yes, good point, Kaper. I did originally have ,2 in there, and then changed it to ,1

Thanks,

Pete

[DawsonG]

Your first suggestion I seemed to run into issues it worked when the drop downs showed a 1.5 hr split, but if it was less it still returned a Y. Once I tried your second one it worked perfectly. I've been doing some trial and error and it seems to be working great! Thank you very much it is very much appreciated. My coworkers think I'm an expert in Excel, I'm good, but there's always better! Thanks again!

(This sheet was needed for a few reasons including that when filled out manually too many people would transcribe things like 1hr15m as 1.15 hrs in our payroll system instead of 1.25 lol.)

[DawsonG]

Thanks for your help! I ultimately used the =if(max(AA16,AA17,AA18)>=5399.5/86400,"Y","N") but I still appreciate the help.

[DawsonG]

very good information to know about the fractions for time and binary, without that tidbit id be going over this endlessly