+ Reply to Thread
Results 1 to 18 of 18

Excel Formula to Show Type of Shift Based on Start and End Time

  1. #1
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Excel Formula to Show Type of Shift Based on Start and End Time

    I believe there is a way to express a Day, Evening and Night shifts with Excel formula using Start time and End time. I have 3 standard shift types: “Day,” “Evening” and “Night.”

    Day Shift information:
    Start time: 7:00am
    End time: 3:00pm

    Evening Shift information:
    Start time: 3:00pm
    End time: 11:00pm

    Night Shift information:
    Start time: 11:00pm
    End time: 6:59am

    For example, if A2 = 7:00am and B2=3:00pm, C2 outcome = Day Shift. The goal is to have a formula that will take the start time (A2 = 7:00am) and the end time (B2=3:00pm) range and return “Day Shift” if A2>=7:00am and B2<=3:00pm. It should cover times that fall between 7:00am and 3:00pm as well (for example, 8:00am - 2:57 pm = Day Shift; 11:00am – 3:00pm = Day Shift).

    If A2>=3:00pm and B2<=11:00pm, the formula should return “Evening Shift” It should cover times that fall between 3:00pm and 11:00pm as well (for example, 5:00pm – 8:00pm = Evening Shift; 8pm – 9:00pm = Evening Shift).

    Lastly, if A2>=11:00pm and B2<=7:00am, the formula should return “Night Shift” It should cover times that fall between 11:00pm and 7:00am as well (for example, 12:00am – 5:00am = Night Shift; 11:50pm – 6:59am = Night Shift).

    The goal is to have a formula that will combine the three scenarios and display “Day,” “Evening” and Night displayed based on the start and end time. Attached is a sample copy for your convenience.

    Thank you in advance for your assistance.
    Attached Files Attached Files
    Last edited by bjnockle; 07-03-2013 at 07:28 PM.

  2. #2
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Excel Formula to Show Type of Shift Based on Start and End Time

    vlady: Please let me know why the post was removed. I do not think I have posted this particular post before now. Thank you in advance for your response.

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Excel Formula to Show Type of Shift Based on Start and End Time

    I think there is a conflict in you data. The start time is also the end time.

  4. #4
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Excel Formula to Show Type of Shift Based on Start and End Time

    Alkey: thank you for the observation. The end time for the night shift should be 6:59am - that is from 11:00pm to 6:59am. This should address the conflict.

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Excel Formula to Show Type of Shift Based on Start and End Time

    Will the times span more than one shift?

    For example:

    Start = 3:00 AM
    End = 12:00 PM
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Excel Formula to Show Type of Shift Based on Start and End Time

    @ bjnockle, from the looks of it, Vlad deleted his own post, probably something he started, and then decided not to post. I dont think it was anything you said or did
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Excel Formula to Show Type of Shift Based on Start and End Time

    Tony: Thank you for the question. The times cannot span more than one shift. For example, 7am - 3pm is one shift (day), 3pm - 11pm is another shift (evening) and 11pm to 6:59am is the third shift. The times are fixed and the times that fall between these times will show the shift type (for example, 3pm -7pm is considered evening shift since it is inclusive in the time span for evening - 3pm - 11pm).

    Thank you in advance for your assistance.

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Excel Formula to Show Type of Shift Based on Start and End Time

    OK, then you need to be more specific when defining the shift times as they overlap.

    For example, you might define then like this:

    7:00 AM to 2:59 PM
    3:00 PM to 10:59 PM
    11:00 PM to 6:59 AM

  9. #9
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Excel Formula to Show Type of Shift Based on Start and End Time

    Tony: thank you again for the response. Yes, the way you defined the shift times is appropriate. Thanks in advance for the assistance.

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Excel Formula to Show Type of Shift Based on Start and End Time

    Crete this 2 column table:

    Start.............Shift
    12:00 AM.......Night
    7:00 AM.........Day
    3:00 PM.........Evening
    11:00 PM.......Night

    Assume that table (less the headers) is in the range I2:J5.

    Then, this formula entered in C2:

    =LOOKUP(A2,I$2:J$5)

  11. #11
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Excel Formula to Show Type of Shift Based on Start and End Time

    Tony: thank you so much for the response. This is close to what exactly I am looking for except that the "Night" portion of the formula is not accurately displaying the shift. However, the first two portions are working great. Formula: =IF(AND(A2>=--"7:00",B2<=--"15:00"),"Day",IF(AND(A2>=--"15:00",B2<=--"23:00"),"Evening",IF(AND(A2>=--"23:00",B2<=--"24:00"),"Night")))

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Excel Formula to Show Type of Shift Based on Start and End Time

    You don't need to test for the end time.

    So, you don't want to use a table?

  13. #13
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Excel Formula to Show Type of Shift Based on Start and End Time

    Tony, thanks again for the response. Yes, I don't want to use a table. Thank you for your assistance in advance.

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Excel Formula to Show Type of Shift Based on Start and End Time

    Try this...

    =LOOKUP(HOUR(A2),{0;7;15;23},{"Night";"Day";"Evening";"Night"})

  15. #15
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Excel Formula to Show Type of Shift Based on Start and End Time

    Tony: This is fabulous! The formula is ONLY looking at the start time and the LOOKUP is acting like a vector to the different start and end times versus the shift types. This does the trick for me! Again, thank you.

  16. #16
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Excel Formula to Show Type of Shift Based on Start and End Time

    You're welcome. Thanks for the feedback!

  17. #17
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Excel Formula to Show Type of Shift Based on Start and End Time

    Or this:
    =IF(AND(A2>=--"7:00",A2<--"15:00"),"Day",IF(AND(A2>=--"15:00",A2<--"23:00"),"Evening",IF(OR(A2>=--"23:00",A2<--"7:00"),"Night")))

  18. #18
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Excel Formula to Show Type of Shift Based on Start and End Time

    Using nested IFs...

    =IF(HOUR(A2)>=23,"Night",IF(HOUR(A2)>=15,"Evening",IF(HOUR(A2)>=7,"Day","Night")))

+ 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