Date range that excludes weekends

[Chris220]

How can I create a formula which excludes weekends from the date range.

for example:

Wk30 Wk31 Wk32 Wk33
Jun 22 - Jul 26 Jul29 - Aug 2 Aug 5 - Aug 9 Aug12 - Aug16


Apologies for not uploading the file as I've been having trouble uploading files recently.

[AliGW]

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[FlameRetired]

In the meantime:
With 1st of month in A2 try this in B2 and fill across.

Please Login or Register  to view this content.

Then this in B1 and filled across.

Please Login or Register  to view this content.

and Custom format as "Wk"0

	A	B	C	D	E	F	G	H
1		Wk27	Wk28	Wk29	Wk30	Wk31	Wk32	Wk33
2	7/1/2019	Jul 1 - Jul 5	Jul 8 - Jul 12	Jul 15 - Jul 19	Jul 22 - Jul 26	Jul 29 - Aug 2	Aug 5 - Aug 9	Aug 12 - Aug 16
3

[Chris220]

Hi FlameRetired

Thanks a lot! The weeks work perfectly, however I'm having a bit of trouble with the dates (B2:H2). Each cell returns '#NAME?'.

[AliGW]

You may not have the TEXTJOIN function - I believe it is in Excel 2019 and the Office 365 subscription versions only.

[Chris220]

I wasn't aware, bummer. Are there alternatives for excel 2016

[AliGW]

Hopefully, although it will be more complex. You will need to wait for someone who knows how.

[Chris220]

Thanks AliGW, I gave it a shot using concatenate. The cell displayed the first day but failed to return the last working day of the week. i'll keep playing around.

=CONCATENATE(TEXT(WORKDAY.INTL(EOMONTH($A2,-1),COLUMNS($B$1:B$1),{"0111111","1111011"}),"mmm d"))

[FlameRetired]

In this case the two formulas need to be concatenated.

Please Login or Register  to view this content.

[Chris220]

Perfect thanks a lot!