I'm trying to come up with a spreadsheet where I can enter a schedule and it will compute estimated earnings. The job has a shift differential so I need to computer the regular hours and the shift differential. If hours are worked after 7pm M-F OR any hours on a Sat/Sun I want to be able to compute those as Differential hours. I currently have a cells that deliniate:
Day of Week (column A)
Start Time (column C)
End Time (column E)
I have the formula for figuring out the differential hours, but I'm struggling with computering the IF/THEN statment to tell excel to NOT computer regular hours on a Saturday or Sunday. Based on the issue I seem to be having, my formulas are not taking into consideration the day of the week for Sat/Sun and skipping that part of my formula for some reason and simply doing the "false" part of my if / then formula. PLEASE HELP!
For my regular hours formula I have: =IF(OR(A3="Sun", A3="Sat"),"0",(E3<C3)*"19:00"+MIN(E3,"19:00")-MIN(C3,"19:00"))
For my differential hours formula I have:=IF(OR(A3="Sat",A3="Sun"),E3-C3,(E3<C3)*("23:30"-"19:00")+MEDIAN(E3,"19:00","23:30")-MEDIAN(C3,"19:00","23:30"))
Bookmarks