If statement to compare time cell to a time

  • Thread starter Thread starter Z-Man-Cek
  • Start date Start date
Z

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!
 
Your time statements are wrong. You need to use commas to seperate, not colons.

E.g, time(6,0,0)
 
Your welcome. Thanks for the feedback!
Click to expand...
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?
 
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?
Click to expand...

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
 
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.
 
Don't suppose you want to help me as well?

=IF(AND(TIME(7,0,0)<=C93,E93=""),"FIRST",IF(AND(TIME(7,0,0)<=C93,TIME(14,0,0)<=E93),"BOTH",IF(AND(TIME(14,0,0)<=C93,E93=""),"SECOND","NA")))

The above is not working for the last condition - if C93 is later than 1400 and E93 is blank.

Thank you so much!!
 
Dear ZMan,

Can you Share the Excel Sheet with me, this is needed for my project

Kindly consider and send it.

Thanks and Regards

Rajesh.D
 
I need to use IF command with Time like
If A1 is lens then 6:00 hours the B1 to print Half Day else Full day

kindly guide the solution
 
Back
Top