Microsoft Excel - I want to select 2 days off and calculate hours only for the remaing 5 workdays

Asked By Dana Banks on 13-Nov-12 09:48 AM
I have a time sheet that works for the whole year.  Our pay periods are two weeks at a time for a total of 26 pay periods a year.  There are seven days for each work week and I want to be able to use like a check box or a radio button to select any two days off for each of the two weeks.

I would want Excel to show only hours worked for the days that were not checked but in seperate cells divided by each day of the week.

In other words, If I select Sunday and Monday off, then the Excel will show shift hours only for the days I worked.

For an example:

Cell B2 I can put a drop down box for shift hours such as 0800-1600 representing whatever standard shift I am on for that pay period.

Cell C2 has radio buttons or check boxes labled Sun - Mon - Tue - Wed etc

If I select Sun and Monday as my days off then I need Cell C3 to show shift hours for Tuesday, it would reference whatever was in Cell B2 which for this example is 0800-1600.

If you need more clarity I'll try to explain further or send a screen shot if needed.

Harry Boughen replied to Dana Banks on 13-Nov-12 05:57 PM
Hello Dana,

The attached file contains something that you might be able to use to do what you want.  It only deals with the workday selection not the hours management.

Hopefully it should be reasonably clear and you can move things around to suit your circumstance.  Let me know if you need more help.

Dana Banks replied to Dana Banks on 15-Nov-12 02:56 PM
Test Time Sheet

Here is the file I'm working in.  the issue is perhaps better cleared up with this.

D5 has 1:00pm and G5 has 9:00pm.

Row 8 has the check boxes and row 37 is where I want my information to show.

If Sun and Monday are false, the in the corresponding boxes in row 37, it would show the shift hours for that day.  if false as Tues and Wednesday show, then it would show no hours.

Thanks Harry