I am trying to write a formula in a calculated column that would populate shift hours base on shift name. The Shift title column is names "ERSShift".

The different shifts available are "Night Shift","Night Shift Weekend/Holiday","Day Shift","Day Shift Weekend/Holiday", "Supervisor Coverage" and "Day Shift".

The Shift "Night Shift" when entered should auto populate "3pm-7am" in my calculated column The Shift "Night Shift Weekend/Holiday" should auto populate "7pm-7am" in my calculated column The Shift "Day Shift Weekend/Holiday" should auto populate "7am-7pm" The remaining 2 shifts have no shift hours associated and can be left blank.

I came up with this formula but it is broke, can you tell me what I am missing?

=IF(ERSShift="Day Shift Weekend/Holiday","7am-7pm",IF(ERSShift="Night Shift","3pm-7am",IF(ERSShift="Night Shift Weekend/Holiday","7pm-7am",""))))

2 Answers 2


Try using below formula:

=IF([ERSShift]="Day Shift Weekend/Holiday","7am-7pm",IF([ERSShift]="Night Shift","3pm-7am",IF([ERSShift]="Night Shift Weekend/Holiday","7pm-7am","")))


  1. Sometimes comma(,) does not work in formula (I am not sure but it is based on something language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).
  2. The choice column values you are using in formula are case sensitive. So make sure you are using the choices exactly as mentioned in column setting.

Official Documentations:

  1. Calculated Field Formulas.
  2. IF function.
  • Hi @Lisa, have you tried this formula? does this work for you? Commented Jul 7, 2020 at 5:45

There is nothing wrong with your formula, but you have typed an extra ")" at the end.

I've tested with your original formula, removed the last ")" and it works fine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.