VLOOKUP? Array? Collect? Match?

kolfinna

Hi!

So--I have basic formula knowledge but feels like I'm outside my wheelhouse.

All I want to do is return a specific start date based on a couple criteria:

Week (1-7, beginning 2/20/20)

Start time (morning, midday, night) Note: all morning work starts on Friday, all midday work starts on Wednesdays, and all night work starts on Mondays. There will be no exceptions. Thus:

• If week = 1 and start time = morning, start date = 2/21/20;
• if week = 1 and start time = midday, start date = 2/26/20;
• if week = 1 and start time = night, start date = 2/24/20.
• ...and so on.

The only way I know how to do this currently is nested IF/AND statements. However, having all that for 21 variations sounds like a ridiculously inefficient way to do things. Would an array work? Or some sort of VLOOKUP table?

Thanks in advance for your time!

 

ron.judenberg112096

Assuming columns for Week and Start Type. try this:

=DATE(2020, 1, 13) + (7 * Week@row) + IF([Start type]@row = "morning", 1, IF([Start type]@row = "Night", 4, 6))

ron.judenberg112096

Assuming columns for Week and Start Type. try this:

=DATE(2020, 1, 13) + (7 * Week@row) + IF([Start type]@row = "morning", 1, IF([Start type]@row = "Night", 4, 6))

kolfinna

Sorry for the late reply--this was perfect. Thanks so much!