Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 ,"") |
#12
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi,
Am Thu, 9 Apr 2015 08:12:53 -0700 (PDT) schrieb : 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 ,"") try: =IF(AND(MOD(A1,1)=0.5,MOD(A1,1)<=0.54097222),C1," ") Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#13
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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. |
#14
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]() |
#15
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
Rất đáng quan tâm những gì bạn chia sẻ.
|
#17
![]() |
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
verify use of TIME Function, Find Quantity Level compare to time-d | Excel Discussion (Misc queries) | |||
IF statement to calculate time usage in specific time bands | Excel Worksheet Functions | |||
compare time | Excel Discussion (Misc queries) | |||
Adding time to date-time formatted cell | Excel Discussion (Misc queries) | |||
compare time in IF statement | Excel Worksheet Functions |