Hello, I’m designing a weekly run sheet for my team, at the top of the sheet i have a drop down with Monday D1, Monday N1 all the way to sunday.

In my hour by hour section of the run sheet lets take the 0700/1900 hour section ive put in the formula bar the IF formula: (the Drop down is E3, the times run hourly separately with a 30 min divider (G7/8 is 07/19 H7/8 is 30 I7/8 would be 08/20 and it runs all the way through like that until we hit 0700 again, a 12 hour shift)

=-IF(OR(E3={"Monday D1","Tuesday D2","Wednesday D3","Thursday D4","Friday D5","Saturday D6","Sunday D7"}),"-7","")&IF(OR(E3={"Monday N1","Tuesday N2","Wednesday N3","Thursday N4","Friday N5","Saturday N6","Sunday N7"}),"19","")

For whatever reason when i click a day shift its changing to 7 but when i click a night shift its not changing to 19, i think it has something to do with the separation of formulas and its not reading it right.

If anyone can find the fix that would be greatly appreciated or if you have an even simpler way of doing this that would be also appreciated.

Edit: I’ve tentatively got it to work be erasing the second IF formula as specified that anything else return false value as 19 but that means if some ding dong decided not to select a day and type whatever then it would still return the 19 false value, i still need it to be for the selections only and if anyone types anything else then it returns the error or ####