how do i perform a logical test on the time portion of a datetime formatted cell

If time is between 7am and 11am = morning
If time is between 12pm and 5 pm = afternoon
If time is > 5pm and < 7pm = Evening
If time is between 7pm and 11pm = Night
If time is between 12am and 6am = midnite

cell evaluated has date and time input 6/4/03 12:36 AM

Hi!

I think on balance I would use a vlookup table. I say "on balance" because times can be awkward beasts!

The following table is the lookup :

0..............................midnight
0.291666667...........morning
0.5...........................afternoon
0.708333333...........evening
0.791666667...........night

Give it a name (Inset > Name > Define) I'll call it TimesOfDay

The numbers in it represent, respectively, 0, 7am (=7/24), noon (=12/24), 5pm (=17/24) and 7pm (=19/24).

If you have a date/time of the kind you describe in A1, then the following in B1 will give its TimeOfDay

=VLOOKUP(A1-INT(A1),TimesOfDay,2,TRUE).

This is based on the Excel way of recording time. The date/time you have in your cell is an integer - the number of days since 1/1/1900 (default system) - and a decimal part which is the fraction of a day (so .5 is 12 hours or 12 noon)

Alf