Need help with formula

[BobHunter]

I need a formula to work out a shift pattern

For example, I have a date/time in A1.

If the time in A1 is between 7pm and 7am it is a nightshift
If the time in A1 is between 7am and 7pm is is a dayshift


Sounds simple but I've have not cracked it yet.

Any help appreciated.

 

[xlbo]

here's a clue

1 day = 1

1 hour = 1/24
1 minute = 1/24/60 (1/1440)
1 second = 1/24/60/60 (1/86400)

midnight = 0
23:59:59 = 0.999

All you need to do is compare the time in A1 to 2 constants that represent 7pm and 7am

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[BobHunter]

Good clue, but still not cracked it.

8am for example, evaluates true >7am (0.29167) but evaluates false <7pm (0.79167).

Same result for 8pm.

I am missing something ?

A1 has 23/09/2005 8:43:57 PM in it, if that helps....

 

[xlbo]

As I said - you must compare to TWO constants

If.... A1 > 7am AND < 7pm THEN "Dayshift" Else "NighShift"

You will need an IF formula either nested to 2 levels or combined with an AND to test this properly

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[BobHunter]

Okay

23/09/2005 8:43:57 PM in A1

If.... A1 > 7am AND < 7pm THEN "Dayshift" Else "NighShift"

A1 > 7am is True
A1 < 7pm is False

?

 

[xlbo]

You are using a Date/Time in A1 rather than just a Time

1 day = 1

therefore today is 38601 (or 38601 days since 1/1/1900)

so midday today is 38601 + 0.5 or 38601.5
6pm today is 38601 + 0.75 or 38601.75

therefore testing the Date & Time > 7am will always be true.

To get th eTime portion of A1, you must use something like

=A1-INT(A1)

and then test that for >7am and < 7pm

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[BobHunter]

Thanks very much - my small brain has finally cracked it...


=IF(AND((A1-INT(A1))>0.2916666666,(A1-INT(A1))<0.791666666),"Days","Nights")