Time Calculations

[cnaylon]

Hi,

I know Excel has issues when using Time. I am trying to determine if a job that was performed by one of my people was performed before or after a certain time so I can pay them for 'After Hours' work.

Here's the formula I'm using: =IF(OR(C9<0800,C9>2100),"After Hours"," "). Cell C9 contains values like: 9:15:00 AM.

If I use this formula, Excel doesn't seem to recognize the time in C9 at all and either makes all the calculations equal "After Hours", or " " depending on what evaluator I use (< or >). If I change the "0800" and "2100" in the formula to "08:00", "21:00", Excel errors out. As if it doesn't recognize the values.

Any thoughts?

Thank You,
Craig

 

[VoG]

Try

=IF(OR(C9<"08:00"+0,C9>"21:00"+0),"After Hours","")

 

[cnaylon]

Perfect. Any explanation on why that actually works?

Thanks so much VoG.

Craig

 

[VoG]

Basically, "08:00" is a text value. Adding 0 to it coerces it to a number that Excel recognises as a time.

 

[cnaylon]

Crazy. Learn something new everyday.

Thanks again, always a pleasure.

Craig