Determining Whether a Worked Shift Was Morning, Afternoon or Night

» Determining Whether a Worked Shift Was Morning, Afternoon or Night

CATEGORY: Excel Date & Time Formulas

VERSIONS: All Microsoft Excel Versions

Problem:

We want to use the sign-in times from column A to determine whether a person was starting a morning, an afternoon, or a night shift.
For times before 12:00, the formula should return "Morning Shift".
For times between 12:00 and 16:00 the formula should return "Afternoon Shift".
For times after 16:00, the formula should return "Night Shift".

Solution:

Use the IF and TIME functions as shown in the following formula:
=IF(A2

Rate this tip



1	2	3	4	5

RATING: 2.67
VIEWS: 8236

Email Tip

Print Tip

Add to My Tips

Where's the rest of this?

cmxsevenfoldxmc wrote on January 24, 2007 12:28 EST

Where's the rest of the tip? I've been looking for this one tip for days and now that I finally find it, it's incomplete. Can the rest be posted up? Or what do I need to do to see the rest?

Thanks

oldchippy wrote on January 25, 2007 06:13 EST

You may have to buy the book to find the answer, it's on page 151, see link

[url]http://www.exceltip.com/pl-f1ff_contents[/url]

or you could place your question in this forum?

daddylonglegs wrote on January 25, 2007 06:25 EST

Don't know what the tip actually says but this is a fairly basic IF formula

=IF(A2>=2/3,"Night",IF(A2>=0.5,"Afternoon","Morning"))&" Shift"

..or with LOOKUP

=LOOKUP(HOUR(A2),{0,12,16},{"Morning","Afternoon","Night"})&" Shift"

Sponsors:

Book Store:

VIEW ALL BOOKS

Recommended Books:

Related MS EXCEL TIPS:

Terms and Conditions of use
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.