Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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?


  #7   Report Post  
Old 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
Default 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.
Please help out.
  #8   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
verify use of TIME Function, Find Quantity Level compare to time-d nastech Excel Discussion (Misc queries) 9 July 11th 07 01:58 PM
IF statement to calculate time usage in specific time bands Daren Excel Worksheet Functions 6 January 31st 07 01:34 PM
compare time Eddie Munster Excel Discussion (Misc queries) 5 October 10th 05 01:27 AM
Adding time to date-time formatted cell tawtrey(remove this )@pacificfoods.com Excel Discussion (Misc queries) 4 August 12th 05 10:53 PM
compare time in IF statement Excel Worksheet Functions 4 July 10th 05 06:18 PM


All times are GMT +1. The time now is 02:50 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017