|
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
Can you calculate whether a time is between two other times to show early,
day or late shifts for example? |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
=IF(AND(A1=--"00:00",A1<--"08:00"),"Early",IF(AND(A1=--"08:00",A1<--"17:00"),"Day","Late"))
-- __________________________________ HTH Bob "Shirley4589" wrote in message ... Can you calculate whether a time is between two other times to show early, day or late shifts for example? |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
Why did you show 17:00 as the switch over between Day and Late rather than
16:00? Rick "Bob Phillips" wrote in message ... =IF(AND(A1=--"00:00",A1<--"08:00"),"Early",IF(AND(A1=--"08:00",A1<--"17:00"),"Day","Late")) -- __________________________________ HTH Bob "Shirley4589" wrote in message ... Can you calculate whether a time is between two other times to show early, day or late shifts for example? |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
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 "Shirley4589" wrote in message ... Can you calculate whether a time is between two other times to show early, day or late shifts for example? |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
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 "Rick Rothstein (MVP - VB)" wrote in message ... 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 "Shirley4589" wrote in message ... Can you calculate whether a time is between two other times to show early, day or late shifts for example? |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
Shorter version:
=LOOKUP(A1*24,{0,8,16},{"Early","Day","Late"}) "Rick Rothstein (MVP - VB)" wrote: 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 "Shirley4589" wrote in message ... Can you calculate whether a time is between two other times to show early, day or late shifts for example? |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
That will assign 08:00 to Early rather than Late (see my follow up posting).
Rick "Teethless mama" wrote in message ... Shorter version: =LOOKUP(A1*24,{0,8,16},{"Early","Day","Late"}) "Rick Rothstein (MVP - VB)" wrote: 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 "Shirley4589" wrote in message ... Can you calculate whether a time is between two other times to show early, day or late shifts for example? |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
That will assign 08:00 to Early rather than Late (see my follow up
posting). That should have said... "That will assign 08:00 to Early rather than DAY" (emphasis added). Rick |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
"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? "Rick Rothstein (MVP - VB)" wrote: That will assign 08:00 to Early rather than Late (see my follow up posting). That should have said... "That will assign 08:00 to Early rather than DAY" (emphasis added). Rick |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
=LOOKUP(A1*24,{0,8,16},{"Early","Day","Late"})
Works for me. A1 = 8:00 AM Formula returns Day. -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... That will assign 08:00 to Early rather than Late (see my follow up posting). That should have said... "That will assign 08:00 to Early rather than DAY" (emphasis added). Rick |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
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 "T. Valko" wrote in message ... =LOOKUP(A1*24,{0,8,16},{"Early","Day","Late"}) Works for me. A1 = 8:00 AM Formula returns Day. -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... That will assign 08:00 to Early rather than Late (see my follow up posting). That should have said... "That will assign 08:00 to Early rather than DAY" (emphasis added). Rick |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
When you drag down to increment the time you're getting into "rounding
issues". -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... 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 "T. Valko" wrote in message ... =LOOKUP(A1*24,{0,8,16},{"Early","Day","Late"}) Works for me. A1 = 8:00 AM Formula returns Day. -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... That will assign 08:00 to Early rather than Late (see my follow up posting). That should have said... "That will assign 08:00 to Early rather than DAY" (emphasis added). Rick |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
What is your exact binary representation of 8/24 or 1/3 or 0.333.... ?
-- David Biddulph "daddylonglegs" wrote in message ... 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? "That will assign 08:00 to Early rather than DAY" (emphasis added). "Rick Rothstein (MVP - VB)" wrote: That should have said... "That will assign 08:00 to Early rather than DAY" (emphasis added). That will assign 08:00 to Early rather than Late (see my follow up posting). |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
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 "T. Valko" wrote in message ... When you drag down to increment the time you're getting into "rounding issues". -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... 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 "T. Valko" wrote in message ... =LOOKUP(A1*24,{0,8,16},{"Early","Day","Late"}) Works for me. A1 = 8:00 AM Formula returns Day. -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... That will assign 08:00 to Early rather than Late (see my follow up posting). That should have said... "That will assign 08:00 to Early rather than DAY" (emphasis added). Rick |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
On Sun, 10 Aug 2008 18:58:07 +0100, "Sandy Mann"
wrote: 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 =LOOKUP(B11*24,{0,8,16},{"Early","Day","Late"} ) 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 |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
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 "Sandy Mann" wrote in message ... 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 =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 Replace @mailinator.com with @tiscali.co.uk "Rick Rothstein (MVP - VB)" wrote in message ... 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 "T. Valko" wrote in message ... =LOOKUP(A1*24,{0,8,16},{"Early","Day","Late"}) Works for me. A1 = 8:00 AM Formula returns Day. -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... That will assign 08:00 to Early rather than Late (see my follow up posting). That should have said... "That will assign 08:00 to Early rather than DAY" (emphasis added). Rick |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
On Sun, 10 Aug 2008 18:31:30 GMT, Lars-Åke Aspelin
wrote: On Sun, 10 Aug 2008 18:58:07 +0100, "Sandy Mann" wrote: 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 =LOOKUP(B11*24,{0,8,16},{"Early","Day","Late" }) 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 Furthermore =(A11=B11) returns TRUE but =(A11-B11=0) returns FALSE Lars-Åke |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
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 Biddulph" wrote: What is your exact binary representation of 8/24 or 1/3 or 0.333.... ? -- David Biddulph "daddylonglegs" wrote in message ... 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? "That will assign 08:00 to Early rather than DAY" (emphasis added). "Rick Rothstein (MVP - VB)" wrote: That should have said... "That will assign 08:00 to Early rather than DAY" (emphasis added). That will assign 08:00 to Early rather than Late (see my follow up posting). |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
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 "Lars-Ã…ke Aspelin" wrote: On Sun, 10 Aug 2008 18:31:30 GMT, Lars-Ã…ke Aspelin wrote: On Sun, 10 Aug 2008 18:58:07 +0100, "Sandy Mann" wrote: 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 =LOOKUP(B11*24,{0,8,16},{"Early","Day","Late" }) 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 Furthermore =(A11=B11) returns TRUE but =(A11-B11=0) returns FALSE Lars-Ã…ke |
IS A TIME BETWEEN TWO TIMES EG 12:00 & 8:00
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 Replace @mailinator.com with @tiscali.co.uk "daddylonglegs" wrote in message ... 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 "Lars-Åke Aspelin" wrote: On Sun, 10 Aug 2008 18:31:30 GMT, Lars-Åke Aspelin wrote: On Sun, 10 Aug 2008 18:58:07 +0100, "Sandy Mann" wrote: 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 =LOOKUP(B11*24,{0,8,16},{"Early","Day","Late" }) 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 Furthermore =(A11=B11) returns TRUE but =(A11-B11=0) returns FALSE Lars-Åke |
| All times are GMT +1. The time now is 02:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com