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
  #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

  #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

  #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

  #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?


  #7   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.
  #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

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  
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
  #10   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


  #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

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 ,"")

  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default If statement to compare time cell to a time

Hi,

I want to calculate the In and out time of employees. At times folks come at 9am (30th March) and leave at 2am(31st march). Hence the total time spent is 17 hours. Unable to apply this formula. Also once i know that the the number of hours spent in the office is 6 hours then autmatically in the other sheet/subsheet it populates "P" or "Present". If less than 6 hours then "H" or "Half Day"

Could any one help please.

--
CONFIDENTIALITY NOTICE: The information contained in this e-mail is for the
intended recipient(s) alone. It may contain privileged and confidential
information that is exempt from disclosure under applicable law. If you
have received this email in error, please notify the sender of the error
and delete this message immediately.
  #15   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


  #16   Report Post  
Junior Member
 
Posts: 1
Default

Rất đáng quan tâm những gì bạn chia sẻ.
  #17   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
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 02: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 05:03 PM.

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

About Us

"It's about Microsoft Excel"