CODEWITHSUNDEEP
excelexcel-formula
mattinwpg
mattinwpg

Reputation: 163

List only workdays in given dynamic month

Haven't found anything on here about this yet... My file is used by staff to track production each month. A cell on the sheet contains the first date of the month - i.e. May 1, 2016

I need to build a table that lists only workdays for the month indicated on the previous sheet (May 1, 2016). i've been able to use the =WORKDAYS function to create a list of workdays and use fill to complete the list of days in that month, but the problem is that it just continues on listing the dates in the subsequent month. I'm just using:

=WORKDAY(C2,1)  May 30
=WORKDAY(D2,1)  May 31
=WORKDAY(E2,1)  June 1

etc, to fill the dates. In my example I want Excel to return "" rather than continue with the next month.

I'm assuming there's some sort of IF statement I can use, but I can't figure out how to avoid circular references if the check needs to check the very cell it is in...

Upvotes: 0

Views: 3084

Answers (3)

Cal
Cal

Reputation: 1

You could filter a dynamic array, e.g.

=LET(DateArray,TEXT(SEQUENCE(365,,"01/01/2025"),"dd/mm/yyyy"),FILTER(DateArray,(WEEKDAY(DateArray)<7)*(WEEKDAY(DateArray)>1)))

DateArray is the name I have given to the array formula using the LET function; it is not required, but makes for easier comprehension.

For simplicity, I have started it on the "01/01/2025" and it is "365" rows (for days), which is then formatted to date.

This is filtered out to only include days which have a Weekday (the number 1 to 7 assigned for each weekday) to be greater than 1 (Sunday) and less than 7 (Saturday), which filters out Saturday and Sunday.

This is versatile as you can plug whatever start date in you want. You can also either find the actual length of a year, or use more complex formulas to determine if the year is a leap year.

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26640

On Sheet1 cell A1 is the date value May 1, 2016

On Sheet2 cell A1 is a header, so actual dates will start in cell A2. In cell A2 is this formula to get the first workday of the month:

=IF(WEEKDAY(Sheet1!A1,2)<6,Sheet1!A1,WORKDAY(Sheet1!A1,1))

Then in cell A3 and copied down is this formula:

=IF(A2="","",IF(MONTH(WORKDAY(A2,1))<>MONTH(A2),"",WORKDAY(A2,1)))

You will get results as shown. Note that I copied it down to cell A31 and it gives proper workday dates and stops at the end of the month:

tigeravatar example for mattinwpg

Upvotes: 2

VBA Pete
VBA Pete

Reputation: 2666

How about this formula:

=IFERROR(IF(TEXT(D2,"yymm")<>TEXT(E2,"yymm"),"", WORKDAY(E2,1)),"")

It checks if month and year in cell D2 equal to month and year in cell E2. If this is not the case, it gives you a blank cell.

Let me know if this works for you.

Upvotes: 0

Related Questions