Alan1234

Week Number of Month Function?

28 posts in this topic

Is there a function for defining the week number of the month.  I don't seem to be able to find such function.  For example, today is October 13th and is the 2nd week of October.  I have found the function that says today is the 5th day of the week but cannot find a function that says this is the 2nd week of October.

Share this post


Link to post
Share on other sites



@Alan1234 There is a function _WeekNumberISO you can look at in the help file that returns the week number for the year. You'd have to do a bit of calculation from that point, as I don't recall seeing any ready-made functions. Typically I go for something like this, borrowed from one of our forum members:

#include <Date.au3>

Local $iDays = _DateDaysInMonth(@YEAR, @MON)

For $a = 1 To $iDays
    $iWeek = Floor(($a - 1) / 7) + 1
    ConsoleWrite($a & ": " & $iWeek & @CRLF)
Next

 

1 person likes this

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

JLogan3o13:  Thanks for the script.  That's what I was coming up with.  That it would have to be crunched.  So I was wondering if I was overlooking the proper function.  I modified your script as follows because I didn't know how to handle the ConsoleWrite function.  However, when I run the script it says we are now in week 5.

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

jguinch: ISO 8601 states that Monday is the first day of the week.  So I believe Sunday 2017-10-01 would be in week 6 of September.  However, I would like to change that to be in week 1 of October.

 

Edited by Alan1234

Share this post


Link to post
Share on other sites

Then try this.

; This is calling the first day of the month as the first day of the first week of the month.
MsgBox(0, @YEAR & "/" & @MON & "/" & @MDAY, "Week: " & Ceiling(@MDAY / 7) & " of " & @MON & " month")

 

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

Malkey:  That looks like it will work.

Edited by Alan1234

Share this post


Link to post
Share on other sites

I also came up with the following.  I looked up how Excel handles this and modified Excel's equation.  The first 3 lines calculate the Julian Date.  I got this from an AutoIt example.  I'm wondering is there an easier way to do this?

 

#include <Date.au3>
#include <MsgBoxConstants.au3>

;Can following 3 lines be simplified?
Local $sJulDate = _DateToDayValue(@YEAR, @MON, @MDAY)
Local $Y, $M, $D
$sJulDate = _DayValueToDate($sJulDate, $Y, $M, $D)

; Extracted from Excel formula
$swkn =INT((6+($D+1-($D-7)))/7)

MsgBox($MB_SYSTEMMODAL, "", "Week " & $swkn)


;Excel formula
;=INT((6+DAY(A1+1-WEEKDAY(A1-5)))/7)

Share this post


Link to post
Share on other sites

I want to thank everyone for helping me solve this problem.

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

Another one as a function. It is assumed that the first day of the week is Monday, but you can also choose another one.  (translated from here)

Hope there are not bugs....

#include <date.au3>
Example()

Func Example() ; example of use
    Local $StartingDate = _NowCalcDate() ; Today
    Local $aSplitDate, $aSplitTime
    For $i = 0 To 365 - @YDAY ; print dates from today to end of year
        $sTempDate = _DateAdd("D", $i, $StartingDate) ; calculate next days
        _DateTimeSplit($sTempDate, $aSplitDate, $aSplitTime)
        If $aSplitDate[3] = 1 Then ConsoleWrite("---------------------------------------------------------------" & @CRLF)
        ConsoleWrite(StringFormat('%' & 9 & 's', _DateDayOfWeek(Mod(_DateToDayOfWeekISO($aSplitDate[1], $aSplitDate[2], $aSplitDate[3]), 7) + 1)))
        ConsoleWrite(' ' & $sTempDate & ' ')
        ConsoleWrite(_WeekNumberInMonth($sTempDate) & '° week in month, ')
        ConsoleWrite(_WeekNumberISO($aSplitDate[1], $aSplitDate[2], $aSplitDate[3]) & "° week of the year" & @CRLF)
    Next
EndFunc   ;==>Example

; ----------------------------------------------------------------------------------------
; This function returns in what week number in the month, the passed date falls into.
; It assumes that the first day of the week is moonday
; if you want another day as first day of week then pass the second optional parameter as:
; 1 for Tuesday, 2 for Wednesday ..... 6 for sunday.
; ----------------------------------------------------------------------------------------
Func _WeekNumberInMonth($sDate, $iFirstDow = 0) ; $iFirstDow -> 0 = moonday ... 6 = sunday

    If Not _DateIsValid($sDate) Then Return SetError(1, 0, -1) ; not a valid date

    Local $aDate1, $aDate2, $aTime

    _DateTimeSplit($sDate, $aDate1, $aTime)
    _DateTimeSplit(_DateAdd("D", $iFirstDow * -1, $sDate), $aDate2, $aTime)

    Return Int((13 + $aDate1[3] - _DateToDayOfWeekISO($aDate2[1], $aDate2[2], $aDate2[3])) / 7)

EndFunc   ;==>_WeekNumberInMonth

 

Edited by Chimp
added months separator in listing

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites

#11 ·  Posted

Alan1234,

When I run this I get Week 2...

#include <Date.au3>
#include <MsgBoxConstants.au3>

;Can following 3 lines be simplified?
Local $sJulDate = _DateToDayValue(2017, 12, 31)
Local $Y, $M, $D
$sJulDate = _DayValueToDate($sJulDate, $Y, $M, $D)

; Extracted from Excel formula
$swkn = Int((6 + ($D + 1 - ($D - 7))) / 7)

MsgBox($MB_SYSTEMMODAL, "", "Week " & $swkn)

How are you defining what a "week" is?

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

#12 ·  Posted

.... if you need it very simplified should be like this:

#include <Date.au3>
#include <MsgBoxConstants.au3>
Local $a, $t

Local $sDate = "2017/10/01"

;Can following 3 lines be simplified?
_DateTimeSplit($sDate, $a, $t) ; your $sDate is split in $a -> [1]Y, [2]M, [3]D

; Extracted from Excel formula
$swkn = Int((13 + $a[3] - _DateToDayOfWeekISO($a[1], $a[2], $a[3])) / 7)

MsgBox($MB_SYSTEMMODAL, "", "Week " & $swkn)

 


small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites

#13 ·  Posted

Chimp,

Nice, however the result for 12/31/2017 is week 5.  Again, what constitutes a week?

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

@kylomas, the result 5 (week nr. 5 of dicember)  for day "2017/12/31" is right.

a week is a range of days from monday to sunday.
Within a month there can fall 4, 5 or 6 weeks even if are not complete weeks.
so, for example, if a week begins in october (say monday 2017/10/30) and ends in november (say sunday 2017/11/05) we say that the days that falls in october (30 and 31 of october) are in the week nr. 6 of october, while the days that falls in november (1, 2, 3, 4, 5 of november) are in the week nr.1 of november.
This is what I understood to be the OP's need.
P.S.
If you run my script from post #10, you can see what happens to weeks that cross 2 months.

Edited by Chimp

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites

#15 ·  Posted

Here is my resultant function for identifying the week number for the month:

 

Function name:  _WeekOfMonth()

Put the number 1 in brackets if week starts on Monday.

Put the number 7 in brackets if week starts on Sunday

 

;////////////////////START FUNCTION/////////////////////////////////////
#include <Date.au3>

Func _WeekOfMonth($sdws)

    ;$sdws = day week starts. 1 for Monday through 7 for Sunday.

    ;Julian Date
    Local $sJulDate = _DateToDayValue(@YEAR, @MON, @MDAY)
    Local $Y, $M, $D
    $sJulDate = _DayValueToDate($sJulDate, $Y, $M, $D)

    ; Week of Month Equation extracted from Excel formula
    $swkn =INT((6+($D+1-($D-$sdws)))/7)

    ;Excel formula
    ;=INT((6+DAY(A1+1-WEEKDAY(A1-5)))/7)

    Return $swkn

EndFunc

;////////////////////END of FUNCTION/////////////////////////////////////

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

Kylomas:

I define the week number for October as from Sunday thru Saturday as follows:

10/1/2017 - 10/7/2017 as week 1

10/8/2017 - 10/14/2017 as week 2

10/15/2017 - 10/21/2017 as week 3

10/22/2017 - 10/28/2017 as week 4

10/29/2017 - 10/31/2017 as week 5

 

Problem is that the first week of November is also in the same week as the fifth week of October.  Not sure how the equations handle this situation.  Any ideas?

Some start the week with Monday and end on Sunday.  My function will also handle this difference.

Edited by Alan1234

Share this post


Link to post
Share on other sites

#17 ·  Posted

Look at dec 2017...


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

#18 ·  Posted

9 hours ago, Alan1234 said:

Problem is that the first week of November is also in the same week as the fifth week of October.

That's simply because weeks and months are overlapping, non-synchronized entities. To solve the issue you must decide by your own convention how to unambiguously classify which "month" a week belongs, where the Saturday is not the last day of a month.

Note that you can see the same issue with week-in-year vs. year. There are two main widely adopted conventions : ISO week and accounting week numbers. Only by convention you can decide that a week having some days in year Y+1 is still the last week in year Y.

The fact that months don't have a contant fixed number of days is irrelevant here: the issue is the same when you consider periods of 3 "things" vs. periods of 11 "things". Only common multiples allow synchronization of the two sequences. Hence one can say that the question is ill-posed from a strict mathematical point of view and it's therefore not surpringing that you find apparent contradictions.

 


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#19 ·  Posted

kylomas,

"Look at dec 2017..."

I'm not experienced enough to know how to look ahead and see how my function will perform in the future.  Would like to learn how if someone is willing to lead me thru it.

Alan,

Share this post


Link to post
Share on other sites

#20 ·  Posted

Note the parameters of the _WeekNumberOfMonth() function in this example.   Past and future dates can be entered, or for convenience, if all the parameters are left blank,  the default current date is used.

With this example you can choose the day of the week you wish to be the starting day of the week.  
For example, say Monday is picked for the starting day of the week,  the occurrence of the first Monday in the month signifies the start of week number one for that month.

When this example is run, see SciTE's output pane for a year's worth of week numbers.

#include <Date.au3>

Local $iDayNumOfSofWeek = 2 ; The day number of the start of the week. (2- Momday)
For $j = 1 To 12
    For $i = 1 To _DateDaysInMonth(2017, $j)
        $a = _WeekNumberOfMonth($iDayNumOfSofWeek, @YEAR, $j, $i)
        ConsoleWrite(_DateDayOfWeek(_DateToDayOfWeek(@YEAR, $j, $i), 1) & @TAB)
        ConsoleWrite("2017/" & $j & "/" & $i & @TAB & "week :" & $a[2] & " of month " & $a[1] & ", " & $a[0] & _
                (_DateToDayOfWeek(@YEAR, $j, $i) = $iDayNumOfSofWeek ? " <-- Start of week" : "") & @CRLF)
    Next
Next

MsgBox(0, "Week Number", @MON & "/" & @MDAY & "/" & @YEAR & " is in week number " & _
        _WeekNumberOfMonth()[2] & " of month " & _WeekNumberOfMonth()[1] & ", " & _WeekNumberOfMonth()[0])


; Parameter: $SofW - Start of Week. 1 - Sunday, 7 - Saturday. (Default 2 - Monday)
; Returns an array $array[2] -- Contains the week number of the month of the input date
;                  $array[1] -- Contains the month that week number of the input date is in.
;                  $array[0] -- Contains the year that week number of the input date is in. (2017/1/1 is in the last week of the previous year when $SofW = 2, Monday)
;
Func _WeekNumberOfMonth($SofW = 2, $Year = @YEAR, $iMonth = @MON, $iDay = @MDAY)
    Local $aRet[3] = [$Year, $iMonth]
    For $i = 1 To _DateDaysInMonth($Year, $iMonth)
        If _DateToDayOfWeek($Year, $iMonth, $i) = $SofW Then ExitLoop
    Next
    Local $SW = $i - 1 ; Day date of the 1st day of the first week of this date's month.
    Local $iDOW = _DateToDayOfWeek($Year, $iMonth, $iDay)
    If $iDay <= $SW Then ; This date is at the end of the previous month's weeks.
        If ($iMonth = 1) Then ; When January
            $aRet[1] = 12 ; Previous month
            $aRet[0] = $Year - 1 ; Previous Year
        Else
            $aRet[1] = $iMonth - 1 ; Previous month
        EndIf
        ; Get the week number of this date as part of the previous month's weeks.
        For $i = 1 To _DateDaysInMonth($aRet[0], $aRet[1])
            If _DateToDayOfWeek($aRet[0], $aRet[1], $i) = $SofW Then ExitLoop
        Next
        $SW = $i - 1 ; Day date of the 1st day of the first week of this date's previous month.
        $aRet[2] = Ceiling(($iDay + _DateDaysInMonth($aRet[0], $aRet[1]) - $SW) / 7) ; Week number
    Else
        $aRet[2] = Ceiling(($iDay - $SW) / 7) ; Week number
    EndIf
    Return $aRet
EndFunc   ;==>_WeekNumberOfMonth

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now