CODEWITHSUNDEEP
excelexcel-formulaexcel-365
Atif
Atif

Reputation: 13

Excel Attendance Sheet repeat friday twice

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.

Upvotes: 1

Views: 194

Answers (1)

P.b
P.b

Reputation: 11415

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

Upvotes: 4

Related Questions