First Date of the Week (1 Viewer)

[gstylianou]

Hi, how can find each first date of the current week using vba? For example today is 25/03/2023 i need to take the result of 20/03/2023

 

[KitaYama]

FirstDayOfWeek = Date - Weekday(Date, vbUseSystem) + 1

 

[Gasman]

Simple math.
Each day has a number.
Default is Sunday as 1.

Look at the WeekDay() function.

 

[gemma-the-husky]

Note that all the date functions can use a non standard week numbering system, in case you week starts on a unusual week day.

 

[gstylianou]

Hi, there is a problem abou

Option Compare Database
Option Explicit
Dim myDate As Date
Public Function GetFirstDayOfWeek() As Date
    Dim currDate As Date
    Dim firstDay As Date
    
    If Me.SmartDate = 0 Then
        currDate = Date ' get current date
        firstDay = currDate - (DatePart("w", currDate, vbMonday) - 1) ' calculate first day of week
    ElseIf Me.SmartDate = 1 Then
        currDate = Me.DateSelecter ' get current date
        firstDay = currDate - (DatePart("w", currDate, vbMonday) - 1) ' calculate first day of week
    End If
    
    GetFirstDayOfWeek = firstDay
End Function
---------------------------------------------------------------------------------------
Function NameDayOfWeek(nDate As Date) As String
    Dim NumDay As Integer
    Dim StrDay As String
    Dim dt As Date
    Dim firstDay As Date
    Dim firstDayWeek As Date
    firstDayWeek = Weekday(nDate, vbMonday)
    
    NumDay = Weekday(nDate, vbMonday)
    Select Case NumDay
        Case 1
            StrDay = 1
        Case 2
            StrDay = 2
        Case 3
            StrDay = 3
        Case 4
            StrDay = 4
        Case 5
            StrDay = 5
        Case 6
            StrDay = 6
        Case 7
            StrDay = 7
        Case Else
            StrDay = 8
    End Select
    NameDayOfWeek = StrDay
End Function

t the week no if starts on 01/01/2023. Its must be Week 52 but with following code shows Week 1

 

[plog]

That doesn't make sense. In 2 ways:

1. How can Jan 1st starting on a sunday belong to the prior year?

2. Even if it did belong to the prior year it would be Week 53, not Week 52.

A year (365 days) spans 53 weeks (365 / 7 = 52 Remainder 1)

 

[sonic8]

That doesn't make sense. In 2 ways:

1. How can Jan 1st starting on a sunday belong to the prior year?

2. Even if it did belong to the prior year it would be Week 53, not Week 52.

It makes perfect sense if you apply the week numbering rules from ISO8601, which I believe @gstylianou is using.
Using ISO8601, the answers to your remarks are:
Re 1: The first week of a year is the week containing the first Thursday of January. [EDIT/PS] and weeks start on Monday.[/EDIT/PS]
Re 2: There were only 52 weeks in 2022.

@gstylianou, take a look my IsoWeekAndYear and IsoWeek functions. The text is in German, as I use the German calendar week rules, but these rules are based on ISO8601 and the functions are implemented accordingly.