Remember Me?

#1
December 6th 07, 03:01 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Dec 2007 Posts: 3
If statement to compare time cell to a time

Hi Friends,

Here's the equation that isn't working:

=if(and(E5<\$A\$2,F5time(06:00:00),F5<=time(14:00:0 0)),"1st
Shift",if(and(E5<\$A\$2,F5time(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!
--
Z-Man

#2
December 6th 07, 03:14 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Dec 2007 Posts: 2,722
If statement to compare time cell to a time

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

E.g, time(6,0,0)
--
Best Regards,

Luke M

"Z-Man-Cek" wrote:

Hi Friends,

Here's the equation that isn't working:

=if(and(E5<\$A\$2,F5time(06:00:00),F5<=time(14:00:0 0)),"1st
Shift",if(and(E5<\$A\$2,F5time(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!
--
Z-Man

#3
December 6th 07, 03:18 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Dec 2007 Posts: 3
If statement to compare time cell to a time

Good Stuff Luke - Thanks. It worked. - Russ
--
Z-Man

"Luke M" wrote:

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

E.g, time(6,0,0)
--
Best Regards,

Luke M

"Z-Man-Cek" wrote:

Hi Friends,

Here's the equation that isn't working:

=if(and(E5<\$A\$2,F5time(06:00:00),F5<=time(14:00:0 0)),"1st
Shift",if(and(E5<\$A\$2,F5time(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!
--
Z-Man

#4
December 6th 07, 03:24 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Dec 2007 Posts: 2,722
If statement to compare time cell to a time

Your welcome. Thanks for the feedback!
--
Best Regards,

Luke M

"Z-Man-Cek" wrote:

Good Stuff Luke - Thanks. It worked. - Russ
--
Z-Man

"Luke M" wrote:

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

E.g, time(6,0,0)
--
Best Regards,

Luke M

"Z-Man-Cek" wrote:

Hi Friends,

Here's the equation that isn't working:

=if(and(E5<\$A\$2,F5time(06:00:00),F5<=time(14:00:0 0)),"1st
Shift",if(and(E5<\$A\$2,F5time(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!
--
Z-Man

#5
June 14th 12, 05:54 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2012 Posts: 1
If statement to compare time cell to a time

On Thursday, December 6, 2007 8:54:03 PM UTC+5:30, Luke M wrote:
Your welcome. Thanks for the feedback!
--
Best Regards,

Luke M

"Z-Man-Cek" wrote:

Good Stuff Luke - Thanks. It worked. - Russ
--
Z-Man

"Luke M" wrote:

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

E.g, time(6,0,0)
--
Best Regards,

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?

#6
June 14th 12, 06:23 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Feb 2011 Posts: 3,514
If statement to compare time cell to a time

#7
August 28th 12, 07:01 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Aug 2012 Posts: 14
If statement to compare time cell to a time

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.
#8
May 27th 14, 11:40 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: May 2014 Posts: 1
If statement to compare time cell to a time

Don't suppose you want to help me as well?

=IF(AND(TIME(7,0,0)<=C93,E93=""),"FIRST",IF(AND(TI ME(7,0,0)<=C93,TIME(14,0,0)<=E93),"BOTH",IF(AND(TI ME(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!!
#9
June 17th 14, 07:14 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2014 Posts: 1
If statement to compare time cell to a time

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
#10
September 9th 14, 10:21 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2014 Posts: 1
If statement to compare time cell to a time

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post nastech Excel Discussion (Misc queries) 9 July 11th 07 01:58 PM Daren Excel Worksheet Functions 6 January 31st 07 01:34 PM Eddie Munster Excel Discussion (Misc queries) 5 October 10th 05 01:27 AM tawtrey(remove this )@pacificfoods.com Excel Discussion (Misc queries) 4 August 12th 05 10:53 PM Excel Worksheet Functions 4 July 10th 05 06:18 PM

All times are GMT +1. The time now is 03:44 AM.