List all the Mondays, Wednesdays and Fridays between Two Date

[PeterDavids]

Dear All

I have a date range and a need a formula that lists all the Mondays, Wednesdays and Fridays in the range? The days of to be in the dd-mmm-yyyy formats.

Thank you
Peter

 

[Dave Patton]

T202006b.xlsm
	A
1	27-04-20
2	Wed 29-Apr-20
3	Fri 01-May-20
4	Mon 04-May-20
5	Wed 06-May-20
6	Fri 08-May-20
7	Mon 11-May-20
8	Wed 13-May-20
1d

Cell Formulas
Range	Formula
A2:A8	A2	=WORKDAY.INTL(A1,1,"0101011")

Check Excel's Help for information on the function.

 

[mikerickson]

In B6, put this CSE formula
=MIN(IF((MOD(A2+COLUMN(A1:G1),7)={2;4;6}),(A2+COLUMN(A1:G1))))

Then in B7 put
=B7+IF(MOD(B7,7)=6,3,2)

And drag that down

 

[gaz_chops]

Or maybe,

C6 =
=A2+CHOOSE(WEEKDAY(A2,2),0,1,0,1,3,2,1)

C7 & copy down =
=D6+CHOOSE(WEEKDAY(D6,2),2,0,2,0,3,0,0,3)

 

[gaz_chops]

T202006b.xlsm
	A
1	27-04-20
2	Wed 29-Apr-20
3	Fri 01-May-20
4	Mon 04-May-20
5	Wed 06-May-20
6	Fri 08-May-20
7	Mon 11-May-20
8	Wed 13-May-20
1d

Cell Formulas
Range	Formula
A2:A8	A2	=WORKDAY.INTL(A1,1,"0101011")

Check Excel's Help for information on the function.

Thanks, I knew there was a formula to do it, just couldn't remember it.

 

[PeterDavids]

Gentlemen,

A big thank you, both sets of formulae works perfectly

Kindest Regards
Peter

 

[PeterDavids]

Struggling to understand the syntax, would it be possible to supply the formula's to list:

1. All the Fridays and Sundays in the range?
2. All the Saturdays and Sundays in the range

based upon the formulas formulated by gazchops

C6 =
=A2+CHOOSE(WEEKDAY(A2,2),0,1,0,1,3,2,1)

C7 & copy down =
=D6+CHOOSE(WEEKDAY(D6,2),2,0,2,0,3,0,0,3)

Thank you

 

[gaz_chops]

Fri & Sat

=A2+CHOOSE(WEEKDAY(A2,2),4,3,2,1,2,0,5)

Choose selects the relevant value, so 1st 2nd etc
So
If A2 = a Monday (Day 1) add 4
If A2 = a Tuesday (Day 2) add 3
If A2 = a Wednesday (Day 3) add 2
etc

=A6+CHOOSE(WEEKDAY(A6,2),4,3,2,1,2,0,5)

 

[PeterDavids]

Dear gaz_chops
Please forgive the ignorance but I just can't get the formula's to get the results I am looking for. Just NOT understanding the formula. Attaching image of the result I'm looking for.

 

[mikerickson]

in B9, put the formula =WORKDAY.INTL(A2,1,"0101011")
in B10, put the formula =WORKDAY.INTL(B10,1,"0101011")
drag B10 down to B16

If you are going with a formula based solution, you will need to drag any formula until there are enough answers for the specified length of time.