+ Reply to Thread
Results 1 to 7 of 7

Determining Whether a Worked Shift Was Morning, Afternoon or Night

  1. #1
    Forum Contributor
    Join Date
    12-07-2004
    Posts
    596

    Determining Whether a Worked Shift Was Morning, Afternoon or Night

    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

  2. #2
    Registered User
    Join Date
    01-24-2007
    Posts
    1

    Exclamation Where's the rest of this?

    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

  3. #3
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    You may have to buy the book to find the answer, it's on page 151, see link

    http://www.exceltip.com/pl-f1ff_contents

    or you could place your question in this forum?
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    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"

  5. #5
    Registered User
    Join Date
    09-20-2010
    Location
    islamabad
    MS-Off Ver
    Excel 2019
    Posts
    83

    Re: Determining Whether a Worked Shift Was Morning, Afternoon or Night

    how to modify this formula to caculate morning from Morning shift 7am to 3pm - 3pm-11pm evening 11pm-7am night shift . I modified it but its not capturing night times correctly

    =LOOKUP(HOUR(A2),{0,15,23},{"Morning","Evening","Night"})&" Shift"

  6. #6
    Registered User
    Join Date
    09-20-2010
    Location
    islamabad
    MS-Off Ver
    Excel 2019
    Posts
    83

    Re: Determining Whether a Worked Shift Was Morning, Afternoon or Night

    I tired this formula and its working fine

    =IF(HOUR(J2)<7,"Night Shift",IF(HOUR(J2)<15,"Morning Shift",IF(HOUR(J2)<23,"Evening Shift","Night Shift")))

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,386

    Re: Determining Whether a Worked Shift Was Morning, Afternoon or Night

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1