Need Several Values to Line up to return a specific value - need formula help, please!

Hi Gurus,

The problem I'm having is the AM / PM crossover at midnight (00:00:00).

Our shifts are 06:00:00 to 17:59:59 and 18:00:00 to 05:59:59, 24/7 and each shift is assigned a number, 1 through 14:

Day of week AM Shift # PM Shift #

Sunday 1 2
Monday 3 4
Tuesday 5 6
Wednesday 7 8
Thursday 9 10
Friday 11 12
Saturday 13 14

I have a massive spreadsheet with a half million lines of data that is returning false values for the time stamps between 00:00:00 and 05:59:59. For example, Saturday night shift starts at 18:00:00. It needs to be assigned as shift 14. Time stamps between 18:00:00 and 23:59:59 are assigned shift 14 - this is good; but because Saturday flips over to Sunday after midnight, data with a time stamp between midnight and 05:59:59 is assigned shift 2 in error. All days of the week with time stamps between midnight and 055959 get assigned the following night's shift # and not its own.

I'm going cross-eyed and could use some expert help, please and thanks!

Sincerely,

George

I've attached a portion of the spreadsheet as an example. There's some text highlighted in yellow that hopefully fleshes out the concept I'm struggling with.Book1.xlsx

Hi Spencer,

The time in column B is the time stamp I need to work with, along with the date in column E - I tried to line both of these values up in the vlookup table listed in columns M:P.

Thank you for looking into this!

George

Hi Spencer,

The values in column E signify the day of the time stamp.

The spreadsheet is a collection of data that represents all system scans by our team of forklift operators.

We categorize the scans by day of week; time; and shift number.

The purpose of the data is to provide us with scan history that shows us which shift numbers are historically the slowest / busiest for shift planning purposes.

In addition to this, the time between scans column provides us with an idea of how much downtime each shift is taking.

The shift number needs to be accurate for us to be able to use this data with confidence for decision-making.

The shift number struggle at the midnight crossover is proving to be a difficult one to figure out.

Thanks Spencer,

George

Hi Kevin,

I'm not sure the MOD function will work in this case. I think I need a nested vlookup function or something that can tie two specifc values together to return a specific result. Here's a summarized version of the work - if the info is unclear, please let me know and I'll do my best to clarify.

Again guys, as always, your help is appreciated!

George

popipipo - brilliant!

I had originally tried using the decimal time values as you had but was not able to make my formulas work. The weekday formula nested with the IF statement isn't a concept I ever would have thought of on my own.

Your solution looks to be the one I was searching for but it's complicated and I don't understand how it works - how is it returning the shift numbers (1-14) without referencing a lookup table and the formula not specifying that 1-14 be returned as a value if the formulated criteria are met?

I know english isn't your first language - I'm hoping you can explain to me how this works - you have no idea how happy I am that you were able to make this work for me and a better understanding of the logic behind the formula will help me apply it in the future.

Thank you!

Sincerely,

George

Willem - great explanation to a creative solution.

The logic behind it will help me apply it in other aspects of my job.

Thank you very much for taking the time to help me - this formula will save me a LOT of time!

Sincerely,

George