Can you calculate whether a time is between two other times to show early,
day or late shifts for example?

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

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?

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?

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?

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?

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?

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

"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

=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

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

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

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

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

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

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

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

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

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

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