If statement to compare time cell to a time

[Z-Man-Cek]

Hi Friends,

Here's the equation that isn't working:

=if(and(E5<$A$2,F5>time(06:00:00),F5<=time(14:00:00)),"1st
Shift",if(and(E5<$A$2,F5>time(14:00:00)),"2nd
Shift",if(and(E5=$A$2,F5<=time(06:00:00)),"2nd Shift","Do Not Count")))

I want it to exclude values prior to 6 AM yesterday and after 6 AM today.
A2 is today. I want it to split the included values into 1st shift and 2nd
shift. 1st shift if from 6:00 AM to 14:00 PM. 2nd Shift is from 14:01 to
6:00 AM today. Column F is formatted as "time" and looks like this
"13:01:15."

Help and Thanks!

 

[Luke M]

Your time statements are wrong. You need to use commas to seperate, not colons.

E.g, time(6,0,0)

 

[Z-Man-Cek]

Good Stuff Luke - Thanks. It worked. - Russ

 

[Luke M]

Your welcome. Thanks for the feedback!

 

[nagu5speed]

Your welcome. Thanks for the feedback!

Hi I have two values in time format.

Its in hh:mm:ss

cell 1 is one time and cell 2 is another.

I need If time of cell 1 greater than cell 2 i want to print "late" in cell 3.
If it cell 1 lesser than cell 2 then "EARLY"
if both the times are equal "Perfect"

How to solve?

 

[GS]

Hi I have two values in time format.

Its in hh:mm:ss

cell 1 is one time and cell 2 is another.

I need If time of cell 1 greater than cell 2 i want to print "late" in cell
3. If it cell 1 lesser than cell 2 then "EARLY"
if both the times are equal "Perfect"

How to solve?

In cell3...


=IF(Cell1>Cell2,"Late",IF(Cell2>Cell1,"Early",IF(Cell1=Cell2,"Perfect","")))

...where you need to substitute the actual cell address for 'Cell1' and
'Cell2' in the formula. This will not account for Cell1/Cell2 being
empty. If this is needed then...


=IF(AND(LEN(Cell1),LEN(Cell2)),IF(Cell1>Cell2,"Late",IF(Cell2>Cell1,"Early",IF(Cell1=Cell2,"Perfect",""))),"")

...where Cell3 will remain empty until both Cell1/Cell2 have content.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[naga rajan]

Thanks Garry.

Am not getting the correct answer when both the cells are equal.
Consider this example.

A1 - start time
B1 - end time.
C1 - time taken
D1 - alloted time
E1 - status

Consider start time as 9:00:00 and end time as 9:15:30
So the difference is 00:15:30
Alloted time is also 00:15:30

If time taken(C1) is greater than alloted time(D1) i want to print "LATE" in E1.
If C1 is less than D1 then print "EARLY"
If both times C1=D1 then perfect.
I get only early or late not perfect when both the times are equal.
I used all your said formulas.
Please help out.