Share via

Date/time convert to shift worked

Anonymous
2010-12-16T17:53:08+00:00

Hello,

I have a completion date cell in Date Time format (12/8/2010 22:25) and would like to convert this to which shift (1<sup>st</sup>, 2<sup>nd</sup> or 3<sup>rd</sup>) they are completed on. The break down is this.

1<sup>st</sup> shift = 6:00 AM – 2:30 PM

2<sup>nd</sup> shift = 2:31 PM – 11:00 PM

3<sup>rd</sup> shift = 11:01 PM – 5:59 AM

What would like the formula to calculate this? Thanks

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

Answer accepted by question author

Anonymous
2010-12-16T18:17:24+00:00

By knowing the numerical value for those times, you can write a lookup table like this:

=LOOKUP(MOD(A2,1),{0,0.25,0.604861111,0.959027777},{"3rd","1st","2nd","3rd"})

Note that for ease of use, it might be better to simply construct a table somewhere with the start times for each shift (don't forget to have 2 entries for 3rd shift) so that you can modify it if need be.

Was this answer helpful?

1 person found this answer helpful.
0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest