Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
comparing time value
I have 3 working shifts...7a-3p, 3p-11p, and 11p-7a
4/1/2008 1:06:00PM --- this should yield to "7a-3p" shift 4/1/2008 1:13:00AM --- "11p-7a" 4/1/2008 7:04:00PM --- "3p-11p" 4/1/2008 7:00:00AM --- "11p-7a" I wanted to assign above shift value to the time listed above so that accordingly. The data I received didn't have the time listed as military time. This is what I've written, but it didn't return the right value. Help please!!! =IF(B2=" 3:00:00PM","7a-3p",IF(B2<=" 7:00:00AM","7a-3p",IF(B2="11:00:00PM","3p-11p",IF(B2<=" 3:00:00PM","3p-11p","11p-7a")))) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
comparing time value
=IF(OR(MOD(B2,1)=--"23:00",MOD(B2,1)<--"7:00"),"11p-7a",IF(MOD(B2,1)=--"15:00","3p-11p",IF(MOD(B2,1)=--"7:00","7a-3p")))
-- __________________________________ HTH Bob "ml" wrote in message ... I have 3 working shifts...7a-3p, 3p-11p, and 11p-7a 4/1/2008 1:06:00PM --- this should yield to "7a-3p" shift 4/1/2008 1:13:00AM --- "11p-7a" 4/1/2008 7:04:00PM --- "3p-11p" 4/1/2008 7:00:00AM --- "11p-7a" I wanted to assign above shift value to the time listed above so that accordingly. The data I received didn't have the time listed as military time. This is what I've written, but it didn't return the right value. Help please!!! =IF(B2=" 3:00:00PM","7a-3p",IF(B2<=" 7:00:00AM","7a-3p",IF(B2="11:00:00PM","3p-11p",IF(B2<=" 3:00:00PM","3p-11p","11p-7a")))) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
comparing time value
Question:
I have 3 working shifts...7a-3p, 3p-11p, and 11p-7a 4/1/2008 7:00:00AM --- "11p-7a" How do you know that is the 11-7 shift and not the 7-3 shift? I think you need to adjust your times so that they don't overlap: 7:00 AM to 2:59:59 PM Or 7:00:01 AM to 3:00 PM -- Biff Microsoft Excel MVP "ml" wrote in message ... I have 3 working shifts...7a-3p, 3p-11p, and 11p-7a 4/1/2008 1:06:00PM --- this should yield to "7a-3p" shift 4/1/2008 1:13:00AM --- "11p-7a" 4/1/2008 7:04:00PM --- "3p-11p" 4/1/2008 7:00:00AM --- "11p-7a" I wanted to assign above shift value to the time listed above so that accordingly. The data I received didn't have the time listed as military time. This is what I've written, but it didn't return the right value. Help please!!! =IF(B2=" 3:00:00PM","7a-3p",IF(B2<=" 7:00:00AM","7a-3p",IF(B2="11:00:00PM","3p-11p",IF(B2<=" 3:00:00PM","3p-11p","11p-7a")))) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
comparing time value
Thanks Bob, I tried it but it gave me the "#VALUE!" and I entered it as an
array function. The problem is the time wasn't entered as military time. It either AM or PM in the time format. I know it looks funny, but for all the ones between 7-9 (single digit), it had a space infront of it. "Bob Phillips" wrote: =IF(OR(MOD(B2,1)=--"23:00",MOD(B2,1)<--"7:00"),"11p-7a",IF(MOD(B2,1)=--"15:00","3p-11p",IF(MOD(B2,1)=--"7:00","7a-3p"))) -- __________________________________ HTH Bob "ml" wrote in message ... I have 3 working shifts...7a-3p, 3p-11p, and 11p-7a 4/1/2008 1:06:00PM --- this should yield to "7a-3p" shift 4/1/2008 1:13:00AM --- "11p-7a" 4/1/2008 7:04:00PM --- "3p-11p" 4/1/2008 7:00:00AM --- "11p-7a" I wanted to assign above shift value to the time listed above so that accordingly. The data I received didn't have the time listed as military time. This is what I've written, but it didn't return the right value. Help please!!! =IF(B2=" 3:00:00PM","7a-3p",IF(B2<=" 7:00:00AM","7a-3p",IF(B2="11:00:00PM","3p-11p",IF(B2<=" 3:00:00PM","3p-11p","11p-7a")))) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
comparing time value
It doesn't matter as long as it is real time, and no need to array enter it.
I got the results that you predicted with those dates. -- __________________________________ HTH Bob "ml" wrote in message ... Thanks Bob, I tried it but it gave me the "#VALUE!" and I entered it as an array function. The problem is the time wasn't entered as military time. It either AM or PM in the time format. I know it looks funny, but for all the ones between 7-9 (single digit), it had a space infront of it. "Bob Phillips" wrote: =IF(OR(MOD(B2,1)=--"23:00",MOD(B2,1)<--"7:00"),"11p-7a",IF(MOD(B2,1)=--"15:00","3p-11p",IF(MOD(B2,1)=--"7:00","7a-3p"))) -- __________________________________ HTH Bob "ml" wrote in message ... I have 3 working shifts...7a-3p, 3p-11p, and 11p-7a 4/1/2008 1:06:00PM --- this should yield to "7a-3p" shift 4/1/2008 1:13:00AM --- "11p-7a" 4/1/2008 7:04:00PM --- "3p-11p" 4/1/2008 7:00:00AM --- "11p-7a" I wanted to assign above shift value to the time listed above so that accordingly. The data I received didn't have the time listed as military time. This is what I've written, but it didn't return the right value. Help please!!! =IF(B2=" 3:00:00PM","7a-3p",IF(B2<=" 7:00:00AM","7a-3p",IF(B2="11:00:00PM","3p-11p",IF(B2<=" 3:00:00PM","3p-11p","11p-7a")))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
straight time, time and a half, and double time | Excel Discussion (Misc queries) | |||
Calculate Ending time using Start Time and Elapsed Time | Excel Worksheet Functions | |||
Comparing time values which have milliseconds in them e.g 10:20:30 | Excel Discussion (Misc queries) | |||
comparing time intervals | Excel Worksheet Functions | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions |