Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > If statement to compare time cell to a time
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
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?
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
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 With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1
Default If statement to compare time cell to a time
On Tuesday, September 9, 2014 at 4:21:14 AM UTC-5, wrote:
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

Hoping someone can help, I need a formula to look at a time date cell

3/17/2015 11:57:30 AM

I need cells that contain a time between 12:00:00 and 12:00:59 to then print a number that is in the cell next to it(cell C). If it does not contain a time between this time, to just leave Blank
=IF(AND(B911TIME(12,0,0),B911<TIME(12,0,59)),C911 ,"")

Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
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.
Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
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!!
Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1
Default If statement to compare time cell to a time
Hello sir I'm Badhur
I now prepared monthly overtime sheet by excel but I have problem of total hour by weekly Example 6:00 Am to 6:00PM this minus 8+1 hour less came answer 3 Hour OT& week end Holyday if working 8 Hour OT I need total complete month

DAY 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
DATE MON TUE WED THU FRI SAT SUN MON TUE WED THU FRI SAT SUN MON TUE WED THU FRI SAT SUN MON TUE WED THU FRI SAT SUN MON TUE WED
TIME IN OUT IN OUT IN OUT IN OUT IN OUT TOT IN OUT IN OUT IN OUT IN OUT IN OUT IN OUT IN OUT TOT IN OUT IN OUT IN OUT IN OUT IN OUT IN OUT IN OUT TOT IN OUT IN OUT IN OUT IN OUT IN OUT IN OUT IN OUT TOT IN OUT IN OUT IN OUT IN OUT IN OUT TOT
Fazal 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 14:00 20.00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 14:00 26.00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 14:00 26.00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 14:00 26.00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 15.00 113.00
Badhur 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 14:00 20.00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 14:00 26.00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 14:00 26.00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 14:00 26.00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 15.00 113.00
Hafiz 6:00 18:00 6:00 18:00 6:00 18:00 6:00 18:00 6:00 14:00 20.00 -54.00 -54.00 -54.00 6:00 18:00 -33.00 -175.00
Kashif -36.00 -54.00 -54.00 -54.00 -45.00 -243.00
Shoaib -36.00 -54.00 -54.00 -54.00 -45.00 -243.00
Irshad -36.00 -54.00 -54.00 -54.00 -45.00 -243.00
Julfekar -36.00 -54.00 -54.00 -54.00 -45.00 -243.00
Ali Ahmad -36.00 -54.00 -54.00 -54.00 -45.00 -243.00
Kursheed -36.00 -54.00 -54.00 -54.00 -45.00 -243.00
Naveed -36.00 -54.00 -54.00 -54.00 -45.00 -243.00
Faisal -36.00 -54.00 -54.00 -54.00 -45.00 -243.00
Syed -36.00 -54.00 -54.00 -54.00 -45.00 -243.00
John -36.00 -54.00 -54.00 -54.00 -45.00 -243.00
Leonel -36.00 -54.00 -54.00 -54.00 -45.00 -243.00
Ferdi -36.00 -54.00 -54.00 -54.00 -45.00 -243.00
Dildar -36.00 -54.00 -54.00 -54.00 -45.00 -243.00
Kithabuddin -36.00 -54.00 -54.00 -54.00 -45.00 -243.00
Sahabuddin -36.00 -54.00 -54.00 -54.00 -45.00 -243.00
Syedullah -36.00 -54.00 -54.00 -54.00 -45.00 -243.00
-36.00 -54.00 -54.00 -54.00 -45.00 -243.00

OT Hour's Riyal
Fazal 113.00
Badhur 113.00 2753.81
Hafiz 113.00 1552.62
Kashif -175.00 -2625.00
Shoaib -243.00 -3491.91
Irshad -243.00 -3491.91
Julfekar -243.00 -2405.70
Ali Ahmad -243.00
Kursheed -243.00 -1366.88
Naveed -243.00 -1215.00
Faisal -243.00 -1215.00
Syed -243.00 -2405.70
John -243.00 -5314.41
Leonel -243.00
Ferdi -243.00
Dildar -243.00 -3645.00 without data also count and put minus sighn please How I prepare same like work sheet for monthly
Reply With QuoteReply With Quote
  #13   Report Post  
Junior Member
  
Posts: 1
Default
Rất đáng quan tâm những gì bạn chia sẻ.
Reply With QuoteReply With Quote
  #14   Report Post  
Banned
  
Posts: 3
Default
CẦM CAVET XE GIÁ CAO NHẤT THỊ TRƯỜNG 0916556949
- Nh*n cầm xe máy , xe tay ga các loại...... Không cần giữ xe .
- Cầm 70- 80% giá trị xe
- Thủ tục nhanh gọn , không rườm r* , có tiền liền .
- Không cầm xe gian, xe không ch*nh chủ, xe tháp.
Điều Kiện :
- Xe Ch*nh Chủ ,biển số Th*nh Phố Hồ Ch* Minh , Chứng minh nhân dân + hộ khẩu th*nh phố + 1 hóa đơn điện phải trùng địa chỉ với nhau mang lên để đối chiếu .
- Người đi cầm phải l* chủ xe v* phải đi ch*nh chiếc xe. LH: 0914776949
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

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 08:56 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement