List Fridays and Saturdays in excel

0 votes

The date 2023-03-01 is in cell K6, and I've entered the Friday formula in cell E11 using Excel formulae.

=IF(MONTH($K$6-MOD(WEEKDAY($K$6,1)-6,7)+IF(WEEKDAY($K$6,1)<6,7,0)+7*(INT((ROW()-11)/2)))=MONTH($K$6),$K$6-MOD(WEEKDAY($K$6,1)-6,7)+IF(WEEKDAY($K$6,1)<6,7,0)+7*(INT((ROW()-11)/2)),"")

And in cell E12, I put the following formula for Saturdays

=IF(MONTH($K$6-MOD(WEEKDAY($K$6,1)-7,7)+IF(WEEKDAY($K$6,1)<7,7,0)+7*(INT((ROW()-11)/2)))=MONTH($K$6),$K$6-MOD(WEEKDAY($K$6,1)-7,7)+IF(WEEKDAY($K$6,1)<7,7,0)+7*(INT((ROW()-11)/2)),"")

Also, the formulae correctly calculate the date 2023-03-01 in cell K6.

Nevertheless, when I changed the date in cell K6 to April 1, 2023, I received an empty return in E11 because the month began on a Saturday. Is it feasible to modify the formula so that cell E12 or E12 does not produce an empty result?

I can adjust the formula in E11 to work with both Fridays and Saturdays

=IF(MONTH($K$6-MOD(WEEKDAY($K$6,1)-6-IF(WEEKDAY(DATE(YEAR($K$6),MONTH($K$6),1),1)=7,1,0),7)+IF(WEEKDAY($K$6,1)<6,7,0)+7*(INT((ROW()-11)/2)))=MONTH($K$6),$K$6-MOD(WEEKDAY($K$6,1)-6-IF(WEEKDAY(DATE(YEAR($K$6),MONTH($K$6),1),1)=7,1,0),7)+IF(WEEKDAY($K$6,1)<6,7,0)+7*(INT((ROW()-11)/2)),"")

But as for cell E12 is not adjusted.

This is my try in cell E12

=IFERROR(IF(MONTH($K$6-MOD(WEEKDAY($K$6,1)-IF(ISNUMBER(E11),7,6)-IF(WEEKDAY(DATE(YEAR($K$6),MONTH($K$6),1),1)=7,1,0),7)+IF(WEEKDAY($K$6,1)<IF(ISNUMBER(E11),7,6),IF(ISNUMBER(E11),7,6)+1,IF(ISNUMBER(E11),7,6))+7*(INT((ROW()-11)/2)))=MONTH($K$6),$K$6-MOD(WEEKDAY($K$6,1)-IF(ISNUMBER(E11),7,6)-IF(WEEKDAY(DATE(YEAR($K$6),MONTH($K$6),1),1)=7,1,0),7)+IF(WEEKDAY($K$6,1)<IF(ISNUMBER(E11),7,6),IF(ISNUMBER(E11),7,6)+1,IF(ISNUMBER(E11),7,6))+7*(INT((ROW()-11)/2)),""),"")

But not accurate in results.

Mar 31, 2023 in Others by narikkadan
• 63,420 points
271 views

1 answer to this question.

0 votes

 Try this:

=TEXT(FILTER(SEQUENCE(DAY(EOMONTH(A4,0)),,EOMONTH(A4,-1)+1,1),WEEKDAY(SEQUENCE (DAY(EOMONTH(A4,0)),,EOMONTH(A4,-1)+1,1),1)>=6),"dd/mm/yyyy")

enter image description here

answered Mar 31, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Multiple find and replace in MS Word from a list in MS Excel

If I understand you correctly, you want ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 63,420 points
385 views
0 votes
1 answer

What are the RGB and HEX codes of the 3 color conditional format in Excel?

In Excel 2016 at least the colors ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,420 points
7,013 views
0 votes
1 answer

Excel - add all X in column and then multiply

Use countif for the count then multiply ...READ MORE

answered Oct 1, 2022 in Others by narikkadan
• 63,420 points
283 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,395 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,472 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
723 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,451 views
0 votes
1 answer

Lock and unlock cells in Excel based on color in all worksheets

The following should function if you actually ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,010 points
400 views
0 votes
1 answer

Excel VBA: Obtain the Column and Row of current element in "For Each" loop

Try this: MsgBox rng.Address(RowAbsolute:=False, ColumnAbsolute:=F ...READ MORE

answered Feb 14, 2023 in Others by Kithuzzz
• 38,010 points
769 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP