Adapting calendar to only show Mon-Thurs of a month

Skrej

Board Regular
Joined
May 31, 2013
Messages
159
Office Version
  1. 365
Platform
  1. Windows
I'm trying to create an attendance roster that will automatically populate as I change the month and year. However, to save space, I only want it to calculate said dates for M-Th (days that we have class).

I'm trying to adapt some previous help I received.

Cell B8 has the following formula to calculate the first weekday of the month:
Excel Formula:
=EOMONTH($A$1,-1)+1+CHOOSE(WEEKDAY(EOMONTH($A$1,-1)+1,2),0,0,0,0,3,2,1)

Cell B9 calculates the next weekday:
Excel Formula:
=IF(CHOOSE(WEEKDAY(B8),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")="Thu",B8+4,IF(CHOOSE(WEEKDAY(B8),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")="Fri",B8+3,IF(CHOOSE(WEEKDAY(B8),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")="Sat",B8+2,IF(CHOOSE(WEEKDAY(B8),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")="Sun",B8+4,B8+1))))

This is copied across to cell W8. Of course, depending on how the calendar falls, I frequently end up with several days in the next month.

I would like to stop calculating dates if the next weekday will be in the next month (or possibly just write an empty string if it's into the next month).

However, with the columns being dynamic, I'm not sure how to reference the last calculated date to see if it's into the next month.

Another calendar I use for other purposes calculates the first day of the month in cell c1 with this formula:
Excel Formula:
=DATE(AB3,@INDEX({1,2,3,4,5,6,7,8,9,10,11,12},MATCH(AB2,monthNames,0)),1)
.

From cells c2-C29,
Excel Formula:
=C7+1
is copied across to simply increase the date by one day, up until you hit the 28th day, at which point
Excel Formula:
=IF(MONTH($AD7+1)>MONTH($C$7),"",$AD7+1)
writes blank cells if the date is in the next month.

However, since that calendar takes into account every day of the month, the columns are static until the 28th day. I'm just unclear on how to reference the previous cell in a row when I can't predict what the column will be.

I've tried nesting another if statement such as
Excel Formula:
=IF(CHOOSE(WEEKDAY(P8),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")="Thu",P8+4,IF(CHOOSE(WEEKDAY(P8),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")="Fri",P8+3,IF(CHOOSE(WEEKDAY(P8),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")="Sat",P8+2,IF(CHOOSE(WEEKDAY(P8),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")="Sun",P8+4,P8+1)))), IF(MONTH($a3+1)>MONTH($B$8),"",$a3+1)

but I run into value errors and circular reference errors.

So, in a nutshell, the calendar should start on the first non-Friday weekday of the month, skip over all subsequent Fridays-Sundays, and stop on the last non-Friday weekday.

I'd appreciate any suggestions.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Upvote 0
Solution
Thank you, that's certainly much tidier than the nested IF functions. It did the trick.

Just for the record, I added the following in A:7:T7 to calculate the day of the week.
Excel Formula:
=IF(B12<>"",CHOOSE(WEEKDAY(B$12),"Sun","Mon","Tue","Wed","Thu","Fri","Sat"),"")
 
Upvote 0
Glad I could help!

And your formula for the day of the week works fine, but here's an alternate one that's a bit shorter:

Excel Formula:
=TEXT(B8,"ddd")
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top