Google sheets formula to calculate hours after a specific time and day-CodePudding

I am new to google sheets, but I have this very simple and basic tracking system for my work schedule. I wish to make a formula so it automatically finds the number of hours on nights and weekends column, after a specific time.

So ex. night hours between 9 pm-11 pm Monday - Friday and weekend hours between 2 pm-8 pm on Saturday and Sunday.

CodePudding user response：

Check if you get the expected hours (sorry for the layout a bit different!) https://docs.google.com/spreadsheets/d/1YuY_8XgMUsPtBPJlBgxDFgz8JKB3lCmn2e6vVEutvBY/edit?usp=sharing

test if we =WEEKDAY(A4,2)>5

day on monday-friday =if(D4,, max(0,min(21/24,C4)-B4))

night on monday-friday =if(D4, ,max(0,C4-max(21/24,B4)))