Vba Excel to write date of the year except for Sunday-CodePudding

i want to write a Vba for excel that allow me to write every 8 rows the date of the year starting from january 2023 till the end of december 2023 (format dd, mm, yyyy) excluding sunday of all the weeks. If i want to reduce the distance of the only rows tha t separates saturday from monday how could i do? attached an Example

Up to now i wrote this routine that writes every date of the year, but it does also consider sunday and the distance of 8 rows from saturday to monday that i would like to reduce to 3 rows as previously said. Thanks

Sub Datesoftheyear()

Dim currentDate As Date
Dim endYear As Date

currentDate = Date
endYear = DateSerial(Year(Now()), 12, 31)
For i = 1 To X Step 8
    Cells(i, 1).Value = Format(currentDate, "dd-mm-yyyy")
    currentDate = DateAdd("d", 1, currentDate)
    If currentDate > endYear Then Exit For
Next i

End Sub

CodePudding user response：

You can determine if a date is Sunday using the Weekday function, or using the DatePart function with Interval:="w"

Then, in your loop, you can test for currentDate being a Sunday, and if it is, advance forward by one day.

Sub Datesoftheyear()

    Dim currentDate As Date
    Dim endYear As Date
    
    currentDate = Date
    endYear = DateSerial(Year(Now()), 12, 31)
    For i = 1 To X Step 8
        Cells(i, 1).Value = Format(currentDate, "dd-mm-yyyy")
        currentDate = DateAdd("d", 1, currentDate)
        
        'If sunday, advance to next day
        If Weekday(currentDate) = vbSunday Then currentDate = DateAdd("d", 1, currentDate)
        
        If currentDate > endYear Then Exit For
    Next i

End Sub

CodePudding user response：

You can use this code.

Offset is defined as constant at the beginning of the sub - like that you can change it without searching within the code.
I added an explicit activesheet.cells(1,1) - you maybe want to adjust that
I set the start date to the January 1st. of current year.
regarding the "Sunday"-check: you have to adapt that to your regional settings. For Germany, e.g. a week starts on monday and sundays weekday = 7 ...

Sub DatesOfTheYear()

'Define row offset between two dates here
Const rowOffset As Long = 3

Dim startDate As Date, endYear As Date, rowDate As Date

Dim i As Long, j As Long

startDate = DateSerial(Year(Now()), 1, 31)
endYear = DateSerial(Year(Now()), 12, 31)

Dim rg As Range
Set rg = ActiveSheet.Cells(1, 1)

For i = 0 To DateDiff("d", startDate , endYear)
    rowDate = startDate   i

    '!!!! 
    '!!! you have to check this for your country settings
    '!!!!!
 
    If Weekday(rowDate, vbMonday) <> 7 Then
        rg.Offset(j * (rowOffset   2)) = Format(rowDate, "ddd")
        rg.Offset((j * (rowOffset   2))   1) = rowDate
        j = j   1
    End If
    
Next i

End Sub

CodePudding user response：

Sub Datesoftheyear()

MyRow = 1
For idt=date To DateSerial(Year(date),12,31)
    If mod(idt,7)<>1 Then
        Cells(MyRow,1).Value = idt
        MyRow = MyRow   8
    End If
Next idt

End Sub