using time format in If function

by George2229 Feb 19, 2007 4:28AM PST

I have to cells with time formats. One with Begin time, and the other with end time, with a cell next to them adding how much time had elapsed between the two.

The problem I'm facing is that I want to have another cell put "LATE" if the time entered is past say 8:00a.m. My question is,what format do you use. I have tried >8:00 but excel gives me an error. I have tried the number format but that doesn't work either.

Any Suggestions?

Discussion is locked

- Collapse -

Re: using time format

by Kees Bakker Feb 19, 2007 6:57AM PST

Let Excel handle this. No need to know that 8:00 PM is .3333333333333.

Just choose a cell (might be in a hidden column, or on another sheet, or to the far right or with a invisible white font, so you don't see it), give it a time format, enter the time you want, and use that to compare with. Use an absolute address (like in $C$3), so you can copy your formulas.

Hope this helps.

Kees

- Collapse -

Thanks Kees

by George2229 Feb 19, 2007 7:31AM PST

Thanks a Million Kees!!!!!

- Collapse -

Hey Kees

by George2229 Feb 19, 2007 8:10AM PST

It worked like a charm. If I were to do this in Access, I have no problem now with the "late" if function. As I would basically do the same thing in there "refer it to another field"....

But the problem I am running into is that the total hours that have elapsed. I cannot format it to time...for example 4.30 hours comes up as .01211111 etc. Is there anyway to format a caluclated field in Access to a time format?

- Collapse -

Use the format function ...

by Kees Bakker Feb 20, 2007 12:49AM PST

with an applicable format like "hh:mm".

No time to try it at the moment, but it should work.

Kees

using time format in If function

CNET Forums

Other Forums

Forum Info