S
Shirley4589
Can you calculate whether a time is between two other times to show early,
day or late shifts for example?
day or late shifts for example?
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Bob Phillips
=IF(AND(A1>=--"00:00",A1<--"08:00"),"Early",IF(AND(A1>=--"08:00",A1<--"17:00"),"Day","Late"))
R
Rick Rothstein \(MVP - VB\)
Why did you show 17:00 as the switch over between Day and Late rather than
16:00?
Rick
16:00?
Rick
R
Rick Rothstein \(MVP - VB\)
Here is another formula for you to consider (note that I used 16:00 for the
switch over between Day and Late)...
If A1 contains a time value only (e.g., 12:34)
**************************************************
=LOOKUP(ROUNDUP(24*A1,9),{0,8,16},{"Early","Day","Late"})
If A1 contains a full date (e.g., 8/10/2008 12:34)
**************************************************
=LOOKUP(MOD(ROUNDUP(24*A1,9),24),{0,8,16},{"Early","Day","Late"})
Rick
switch over between Day and Late)...
If A1 contains a time value only (e.g., 12:34)
**************************************************
=LOOKUP(ROUNDUP(24*A1,9),{0,8,16},{"Early","Day","Late"})
If A1 contains a full date (e.g., 8/10/2008 12:34)
**************************************************
=LOOKUP(MOD(ROUNDUP(24*A1,9),24),{0,8,16},{"Early","Day","Late"})
Rick
R
Rick Rothstein \(MVP - VB\)
The reason I used the ROUNDUP function was to correct the entry at 8:00
which, because its time value is 0.3333... and when multiplied by 24 is ever
so slightly less than 8 which causes it to be incorrectly assigned to Early
rather than Day. Here is a more efficient formula to handle that lone
situation which doesn't incur the extra function call....
If A1 contains a time value only (e.g., 12:34)
**************************************************
=LOOKUP(24*A1+0.01,{0,8,16},{"Early","Day","Late"})
If A1 contains a full date (e.g., 8/10/2008 12:34)
**************************************************
=LOOKUP(mod(24*A1+0.01,24),{0,8,16},{"Early","Day","Late"})
Rick
which, because its time value is 0.3333... and when multiplied by 24 is ever
so slightly less than 8 which causes it to be incorrectly assigned to Early
rather than Day. Here is a more efficient formula to handle that lone
situation which doesn't incur the extra function call....
If A1 contains a time value only (e.g., 12:34)
**************************************************
=LOOKUP(24*A1+0.01,{0,8,16},{"Early","Day","Late"})
If A1 contains a full date (e.g., 8/10/2008 12:34)
**************************************************
=LOOKUP(mod(24*A1+0.01,24),{0,8,16},{"Early","Day","Late"})
Rick
T
Teethless mama
Shorter version:
=LOOKUP(A1*24,{0,8,16},{"Early","Day","Late"})
=LOOKUP(A1*24,{0,8,16},{"Early","Day","Late"})
R
Rick Rothstein \(MVP - VB\)
That will assign 08:00 to Early rather than Late (see my follow up posting).
Rick
Rick
R
Rick Rothstein \(MVP - VB\)
That will assign 08:00 to Early rather than Late (see my follow up
That should have said...
"That will assign 08:00 to Early rather than DAY" (emphasis added).
Rick
posting).
That should have said...
"That will assign 08:00 to Early rather than DAY" (emphasis added).
Rick
D
daddylonglegs
"That will assign 08:00 to Early rather than DAY" (emphasis added).
When I tried it 08:00 was assigned to Day. Although 08:00 = 0.33333 that
doesn't affect Excel's binary-based calculation, surely?
T
T. Valko
=LOOKUP(A1*24,{0,8,16},{"Early","Day","Late"})
Works for me.
A1 = 8:00 AM
Formula returns Day.
Works for me.
A1 = 8:00 AM
Formula returns Day.
R
Rick Rothstein \(MVP - VB\)
Interesting... it works for me too **IF** I type the time value 8:00
directly into the cell. HOWEVER, when I tested the formula, I put 7:50 in
A1, 7:51 in A2, selected both and copied them down for a total of 20 rows...
when you do it that way, the formula generates "Early" for the time of 8:00
that was series expanded into A11 (I'm using XL2003 if that matters). The
modification I used corrects that... and works for the directly entered time
value too.
Rick
directly into the cell. HOWEVER, when I tested the formula, I put 7:50 in
A1, 7:51 in A2, selected both and copied them down for a total of 20 rows...
when you do it that way, the formula generates "Early" for the time of 8:00
that was series expanded into A11 (I'm using XL2003 if that matters). The
modification I used corrects that... and works for the directly entered time
value too.
Rick
T
T. Valko
When you drag down to increment the time you're getting into "rounding
issues".
issues".
S
Sandy Mann
Even more interestingly if I do what you say in XL97 and then enter 8:00 in
B11, =A11-B11 returns 0.000000000000000 but as you say
=LOOKUP(A11*24,{0,8,16},{"Early","Day","Late"})
returns "Early" and
Returns "Day"
--
???
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
B11, =A11-B11 returns 0.000000000000000 but as you say
=LOOKUP(A11*24,{0,8,16},{"Early","Day","Late"})
returns "Early" and
=LOOKUP(B11*24,{0,8,16},{"Early","Day","Late"})
Returns "Day"
--
???
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
R
Rick Rothstein \(MVP - VB\)
While the OP is more than likely going to type the value in (so that the
+0.01 correction would not be necessary for that usage), I am struck by how
prevalent the "rounding issue" is. Try using these A1, A2 series
expansions...
A1: 7:40 --- 7:00 --- 6:00 --- 0:00
A2: 7:50 --- 7:30 --- 7:00 --- 4:00
In each case, the generated value for 8:00 will return "Early" when used in
the formula (without the correction). I almost seems that the only time it
doesn't generate "Early" is when it is typed in, or calculated, directly.
Rick
+0.01 correction would not be necessary for that usage), I am struck by how
prevalent the "rounding issue" is. Try using these A1, A2 series
expansions...
A1: 7:40 --- 7:00 --- 6:00 --- 0:00
A2: 7:50 --- 7:30 --- 7:00 --- 4:00
In each case, the generated value for 8:00 will return "Early" when used in
the formula (without the correction). I almost seems that the only time it
doesn't generate "Early" is when it is typed in, or calculated, directly.
Rick
L
Lars-Åke Aspelin
Even more interestingly if I do what you say in XL97 and then enter 8:00 in
B11, =A11-B11 returns 0.000000000000000 but as you say
=LOOKUP(A11*24,{0,8,16},{"Early","Day","Late"})
returns "Early" and
Returns "Day"
=A11*24-B11*24 returns -8.88178E.15
=24*(A11-B11) returns -7.99361E-15
=1*(A11-B11) returns -3.33067E-16
Lars-Åke
R
Rick Rothstein \(MVP - VB\)
The difference between the two appears to be in the digits not displayed
(you are seeing the "rounded for display" value). To see this, use this
formula instead...
=100000*(A11-B11)
Rick
(you are seeing the "rounded for display" value). To see this, use this
formula instead...
=100000*(A11-B11)
Rick
L
Lars-Åke Aspelin
=A11*24-B11*24 returns -8.88178E.15
=24*(A11-B11) returns -7.99361E-15
=1*(A11-B11) returns -3.33067E-16
Lars-Åke
Furthermore
=(A11=B11) returns TRUE
but
=(A11-B11=0) returns FALSE
Lars-Åke
D
daddylonglegs
What is your exact binary representation of 8/24 or 1/3 or 0.333.... ?
David, thanks, that was a stupid comment on my part......1/3 is also a
repeating binary fraction as you surely know, i.e. 0.010101....
Next time I might take my metaphorical foot out of my metaphorical mouth
before I put virtual pen to virtual paper........
David, thanks, that was a stupid comment on my part......1/3 is also a
repeating binary fraction as you surely know, i.e. 0.010101....
Next time I might take my metaphorical foot out of my metaphorical mouth
before I put virtual pen to virtual paper........
D
daddylonglegs
Is the issue more to do with filling down the times rather than rounding? See
this link
http://support.microsoft.com/kb/211830/en-us#appliesto
although it doesn't appear to apply to XL97
this link
http://support.microsoft.com/kb/211830/en-us#appliesto
although it doesn't appear to apply to XL97
S
Sandy Mann
although it doesn't appear to apply to XL97
nevertheless it does - I suppose that MS just can't believe that some of us
are still using XL97
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
nevertheless it does - I suppose that MS just can't believe that some of us
are still using XL97
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.