I have this monthly work report sheet. And I don't know how to make some autofill for column B which will provide a number of a day for a right name of a week day in column A.

I was only able to find formula which gave me date of first work day in first week of the month.

This will take month and year from B2 and B3 cells and generate first day in month:

=TEXT(DATEVALUE(CONCATENATE(1;B2;B3));"dd.mm.yyyy")

This will find from first day in mnoth the first working day.

=TEXT(WORKDAY(EOMONTH(R2;-1); 1);"dd.mm.yyyy")

But the first problem is that the each next month a first work day starts in different name of a week. And second problem is that the each week has a "Sum:" after each friday.

Please, can someone provide me any solution via formulas or macro?

CodePudding user response：

This is really poorly written tbh, but maybe you can make use of it.

Basically, just select the cell that has the first day of the month and enter the date:

The code will then use the current date and work its way down your list until it runs into Sum: at the bottom or the current month ends and put in the date using dd.mm format (feel free to change this in the code).

If it finds Sum: to its left, it will add 3 days to its current date.

Otherwise, it will add 1 day to its current date.

Worth noting that it looks for the name of the current month in cell B2

If you want to add a period after the format (like you showed in your screenshot), just add an extra . to the line Format(aDate, "dd.mm")

It would become ActiveCell.Value = Format(aDate, "dd.mm.")

Sub GenerateDates()
Dim aDate
aDate = InputBox("Enter Date of Current Cell", "Enter Date of Current Cell")
If IsDate(aDate) Then
    aDate = DateValue(aDate)
    Do While ActiveCell.Value = ""
        ActiveCell.Value = Format(aDate, "dd.mm")
        ActiveCell.Offset(1, 0).Select
        If ActiveCell.Offset(0, -1).Value = "Sum:" Then
            aDate = DateAdd("d", 3, aDate)
            ActiveCell.Offset(1, 0).Select
        Else
            aDate = DateAdd("d", 1, aDate)
        End If
        If MonthName(Month(aDate)) <> Range("B2").Value Then Exit Sub
    Loop
Else
    MsgBox "Invalid Date", vbCritical, "Invalid Date!"
    Exit Sub
End If
End Sub

Of course, this could be modified to assume that you select the first of the month on the cell - or it could attempt to figure out the first of the month for you to begin by starting at the top, but this just seemed simple enough.

CodePudding user response：

Display the date in reference to the weekdays

Let's use an Excel formula:

=LET(
WeekRange; A6:A34;
WeekInterval; 6;
ThisMonth; MATCH(B2; {"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"};);
ThisYear; B3;
WeekNames; {"Monday";"Tuesday";"Wednesday";"Thursday";"Friday"};
FirstDay; DATE(ThisYear; ThisMonth; 1);
LastDay; EOMONTH(FirstDay; 0);
FirstWorkDay; WORKDAY(EOMONTH(FirstDay; -1); 1);
WeekShift; WEEKDAY(FirstWorkDay; 2);
WeekInMonth; QUOTIENT(ROW(WeekRange) - INDEX(ROW(WeekRange); 1); WeekInterval);
WorkDays; IFNA(FirstWorkDay - WeekShift   MATCH(WeekRange;WeekNames;0)   7*WeekInMonth; "");
TEXT(WorkDays; "[<" & FirstDay & "] ;[>" & LastDay & "] ;dd.mm."))

Details:

• WeekInterval - the number of cells between the same weekdays
• WeekShift - the weekday of the first workday of the month starting from Monday
• WeekInMonth - a week number in WeekRange
• IFNA(...; "") - put an empty string next to the "Sum:"
• "[<FirstDay] ;[>LastDay] ;dd.mm." - display a space when a date is outside the month (keep spaces after closing square brackets ])
• I use semicolon ; as a separator due to my locale settings.

This formula has to be placed once next to the first Monday of the WeekRange (in your case in a cell B6). The result will be spilled on weekdays.