1

I have made an attendance sheet.

My excel sheet contains following formula in cell H7

=DATE(E5,@INDEX({1,2,3,4,5,6,7,8,9,10,11,12},MATCH(D5,monthNames,0)),1)

Where cell D5 contains Month Name and E5 contains Month Year

In cell H6 contains following formula

=@IF(H7="","",INDEX({"Su";"M";"Tu";"W";"Th";"F";"Sa"},WEEKDAY(H7,1)))

Now I will add 36 columns to complete month and extra columns because I want it to add another friday in next column every time friday comes and after adding extra friday column the formula should continue pattern of days and dates as saturday, sunday, monday, tuesday, wednesday, thursday.

I couldn't find any solution so far.

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
Atif
  • 13
  • 2
  • 1
    Wrap your function in an if() and if the result is “friday” then add another “friday”, if not continue. – Solar Mike Jun 30 '23 at 04:44
  • 1
    *"Now I will add 36 columns to complete month and extra columns because I want it to add another friday in next column every time friday comes and after adding extra friday column the formula should continue pattern of days and dates as saturday, sunday, monday, tuesday, wednesday, thursday."* Can you visualise what that means by adding a table (https://www.tablesgenerator.com/markdown_tables) or screenshot to your post? Also is there a specific reason why you're not using `=IF(H7="","",TEXT(H7,"ddd"))` in `H6` – P.b Jun 30 '23 at 08:23

1 Answers1

5

Not sure if I understood the question correctly.

What I think you're trying to create is a sequence of days within a month where each Friday is repeated (listed twice within the sequence).

This could be achieved in Microsoft 365 using the following formula:

=LET(start, 1&D5&E5,
     dates, SEQUENCE(DAY(EOMONTH(start,0)),,start),
TOROW(dates+IF(MOD(dates,7)=6,{0,0},{0,""}),2))

enter image description here

What this formula does is the following:

First start is declared. This is a concatenation of 1, Month name in D5 and the year number in E5. Excel recognizes this as a date and converts it to the date value. More variations of this can be found in this answer by Mayukh: https://stackoverflow.com/a/76548999/12634230)

Secondly dates is creating a sequence of the number of days within the month of the start date start.

Finally we make use of MOD to check if the date withing the sequence dates is a Friday (MOD result 6 = Friday).

If TRUE it adds 0 to the dates-value (to keep the date value the same) and another time to the column next to it (I chose to have the dates-sequence be vertical, so we add another horizontally, to not create a spill error to the next value in the sequence) If false

If FALSE it adds 0 to the dates-value, to keep it the same and it adds "" to it, which is blank text. Adding this blank to the date value results in an error-value, which is deliberately.

The TOROW function takes the new sequence with it's extra error-and Friday-values and unpivots it to a single row; the 2 argument is to skip error values, so it only unpivots the Friday-values.

Below a screenshot of the formula prior to applying TOROW for illustration purposes enter image description here

P.b
  • 8,293
  • 2
  • 10
  • 25
  • 1
    Pretty nice. The only thing I would change is to replace the MOD logic with a WEEKDAY function, so that its more clear what you're doing. The end result should be the same. – Frank Ball Jun 30 '23 at 14:17
  • Yes, exactly what I want is to create is a sequence of days within a month where each Friday is repeated (listed twice within the sequence). But,the days and dates should be in separate rows just like my sheet, the day starting from cell H6 and continue to I6,J6,K6 and so on whereas the dates are starting in the rows below i.e. H7 and continue to I7,J7,K7 and so on. – Atif Jun 30 '23 at 16:57
  • 1
    Great your formula is working and also I'm able to get the days and dates separately on two rows using format cells and one of the previous formula. I just wanted to know if I want to repeat one or more days just like Friday, I want to repeat Friday and Wednesday as well then what changes will you suggest in the formula? – Atif Jun 30 '23 at 17:47
  • 1
    Then you need to use a LAMBDA to iterate through the days or use Matrix Multiplication (MMULT) for instance: `=LET(start,1&D5&E5,dates,SEQUENCE(DAY(EOMONTH(start,0)),,start),TOROW(dates+IF(MMULT(--({4,6}=MOD(dates,7)),{1;1}),{0,0},{0,""}),2))` – P.b Jun 30 '23 at 19:00
  • 2
    @FrankBall that may be easier to follow the logic. I use MOD simply because it keeps the formula shorter. Using WEEKDAY would be `=LET(start, 1&D5&E5, dates, SEQUENCE(DAY(EOMONTH(start,0)),,start), TOROW(dates+IF(WEEKDAY(dates)=6,{0,0},{0,""}),2))` – P.b Jun 30 '23 at 19:04