I am trying to get a formula into excel that will return a time frame based on the Shift entered in a cell. I see a lot of questions doing the opposite, taking a time frame and returning Shift 1, Shift 2 or Shift 3, but I can't seem to work the formulas backwards to fit what I'm working towards.

For reference the Shift Column, if 1 should return 6:45 am to 2:45 pm 2 should return 2:45 pm to 10:45 pm 3 should return 10:45 pm to 6:45 am

Excel Shift Snippet

CodePudding user response：

Two solutions:

• Use the INDEX function: store the 3 shift timeframes in range Q3:Q5 and find the correct time from it. Here is what it gives: screenshot_solution_formula (I've added two more columns for start and end in case it helps, but no problem if we remove them). Below is the formula to put in L2 and to pull.

L2 = INDEX(Q$3:Q$5,NUMBERVALUE(LEFT($K2,1)),0)

• Use the CHOOSE function to choose from different timeframes specified in formula, by putting this in L2:

L2 = CHOOSE(NUMBERVALUE(LEFT($K2,1)),"6:45 am to 2:45 pm"," 2:45 pm to 10:45 pm", "10:45 pm to 6:45 am")