Share via

Determine which shift it is based on time in Excel

Anonymous
2022-01-14T21:40:17+00:00

Hi,

I am looking for a way in Excel to determine which shift it is given date and time (currently all manual work, so would save a lot of time).

There are 4 different shifts, all working on 4on 4off shift pattern(will use 2 on 2 off for the purpose of this post).

example below:

14.01.2022 between 07:00:00 and 18:59:59 is day shift 1

14.01.2022 between 19:00:00 and 23:59:59 is night shift 1

15.01.2022 between 00:00:00 and 06:59:59 is night shift 1

15.01.2022 between 07:00:00 and 18:59:59 is day shift 1

15.01.2022 between 19:00:00 and 23:59:59 is night shift 1

16.01.2022 between 00:00:00 and 06:59:59 is night shift 1

16.01.2022 between 07:00:00 and 18:59:59 is day shift 2

16.01.2022 between 19:00:00 and 23:59:59 is night shift 2

17.01.2022 between 00:00:00 and 06:59:59 is night shift 2

17.01.2022 between 07:00:00 and 18:59:59 is day shift 2

17.01.2022 between 19:00:00 and 23:59:59 is night shift 2

18.01.2022 between 00:00:00 and 06:59:59 is night shift 2

18.01.2022 between 07:00:00 and 18:59:59 is day shift 1 - and so on

Is this even possible? if yes, how?!

Much appreciated!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points
    2022-01-14T22:28:11+00:00

    Let's say the first day shift starts on January 14, as in your example.

    With a date and time in cell D2, enter the following formula in another cell in row 2:

    =IF(HOUR(D2-TIME(7,0,0))<12,"Day","Night")&" Shift "&MOD(INT(D2-TIME(7,0,0))-DATE(2022,1,14),4)+1

    This can be filled down.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. HansV 462.6K Reputation points
    2022-01-24T19:18:23+00:00

    Sorry, I don't understand your question,

    Was this answer helpful?

    0 comments
  3. Anonymous
    2022-01-24T15:43:00+00:00

    That is really helpful, Thank you!

    I have set it to show shifts 1-8 and whenever the returned value is shift 1,2,3,4 i then manually turn it into Shift 1 and if the returned value is 5,6,7 or 8, i then change it to shift 2.

    is there a way this could be done using the formula?

    Rejected By shift
    Day 1 Day Shift 4
    Night 2 Night Shift 6
    Night 2 Night Shift 7
    Night 2 Night Shift 8
    Day 1 Day Shift 1
    Night 1 Night Shift 1
    Day 1 Day Shift 2
    Day 2 Day Shift 5
    day 2 Day Shift 6

    Was this answer helpful?

    0 comments