Problem with date calculation

[bourny]

Hi,

 

I am trying to put together a script that works out dynamically the UK public holidays.  So far I have managed to pull together a good working script using some sources from the forum and also some of my own code.  I have found a good bit of code that seems to deliver what I am looking for for calculating the last Monday a month however it seems to have a flaw which I cannot work out why.

 

The problem is noticeable when I set the month to September or April.  If I set the month to 9 it correctly tells me there is 30 days however the loop determines it needs to work with 31 days and then gets the returned last days completely wrong.

If I set the month to April it again thinks there are 31 days but this time only then omits the day name from the returned array.

Can anyone assist me working out why the sub loop is getting the number of days wrong .  My research tells me its the way MOD is used and cannot be relied upon for this type of calculation however I am unsure as I have little experience of the MOD function.

Here is the script I am using to work out the last Monday of the month

 

Source of code : 

Here is the code I have chosen from the topic that is as close to what I need to achieve but seems to have the 31 day flaw.

#include<date.au3>
#include<array.au3>

$Year = 2016
$month = 4


$DaysInMonth = _DateDaysInMonth($year, $month)   ;how many days the current month has

msgbox(0, $DaysInMonth, "")
$LastDayInMonth = _DateToDayOfWeek($year, $month, $DaysInMonth)       ;last day in the month (date format)



Dim $LastNamedDays[7][2]                                            ;array of [dates][last Weekday in month]

For $i = 0 To 6
    $LastNamedDays[$i][0] = $year&"/"&$month&"/"&_DateDaysInMonth(@YEAR, @MON)-$i
    If $LastDayInMonth - $i > 0 Then
        $LastNamedDays[$i][1] = "Last "&_DateDayOfWeek(Mod($LastDayInMonth-$i,7))
    Else
        $LastNamedDays[$i][1] = "Last "&_DateDayOfWeek(Mod($LastDayInMonth-$i,7)+7)
    EndIf
Next

_ArrayDisplay($LastNamedDays)

 

Many Thanks for your assistance and I will post my completed code that has brought together some good code to create a function to return the array of the UK public holidays once I fix this issue.

 

 

[bourny]

I may have already fixed it .

I should have also changed the below @year and @month macro.  Still need to work out how to filter out the missing 31st entry on the April month

$LastNamedDays[$i][0] = $year&"/"&$month&"/"&_DateDaysInMonth(@YEAR, @MON)-$i

[bourny]

Should be

$LastNamedDays[$i][0] = $year&"/"&$month&"/"&_DateDaysInMonth($year, $month)-$i

 

[Gianni]

You can use _DateToDayOfWeek

#include<date.au3>
#include<array.au3>

$Year = 2016
$month = 4

$DaysInMonth = _DateDaysInMonth($Year, $month) ;how many days the current month has

MsgBox(0, $DaysInMonth, "")
$LastDayInMonth = _DateToDayOfWeek($Year, $month, $DaysInMonth) ;last day in the month (date format)

Dim $LastNamedDays[7][2] ;array of [dates][last Weekday in month]

For $i = 0 To 6
    $LastNamedDays[$i][0] = $Year & "/" & $month & "/" & _DateDaysInMonth($Year, $month) - $i
    $LastNamedDays[$i][1] = "Last " & _DateDayOfWeek(_DateToDayOfWeek($Year, $month, $DaysInMonth - $i))
Next

_ArrayDisplay($LastNamedDays)

 

Edited May 9, 2016 by Chimp
changed @year and @mon with $Year and $month

[bourny]

Chimp: That works better.  Even after fixing my lack of substituting all @month macros with a variable the code I had picked up is still buggy.  If I test December as a month it bring in December has 31 days but fails to return what the 31st day is.  

 

Your amendment of this code does not have the same issue and does it in less code.  Nice work and thank you for your assistance in this problem.

 

[Gianni]

you are welcome

[bourny]

I have attached my completed code to return an array of UK only public holidays.  all you need to do is change the year being passed into the function and script will do the rest.  

Note:  The purpose of this thread was to allow me to return UK only public holidays and does not extend to other non UK public holidays, however I am sure the code could be adapted to suit that of any country.

 I hope you find it useful as it brings together some various snippets of code to create a overall public holiday calculator with no hard coding.  Only issue I have come across is it did not take into account the queens jubilee in 2012 when the dates were deliberately moved violating the standard UK calculation for bank holidays.

If you need your script to be dynamic using the current year simply substitute "2016" with @year.  The purpose of having the year hard coded in the function caller is to test the script works as expected. 

Here it is.  (I am sure there are others out there that could do the below in 10 lines but this is my go at it and it works just fine but I welcome anyone who can demonstrate their skills in doing the below in less code as it helps us all learn to be better scripters)

expandcollapse popup

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


$aUK_PublicHolidays = GetPublicHolidays("2016")
_ArrayDisplay($aUK_PublicHolidays)




Func GetPublicHolidays($year)

   Dim $aPublicHolidays[0]

   ;January Holidays
   Local $NewYearsDay = _DateDayOfWeek(_DateToDayOfWeek($year, 01, 01))
   Select
      Case $NewYearsDay = "Saturday"
         $sNewDate = _DateAdd( 'd',2, $year & "/" & 01 & "/" & 01)
         $aNewDate = StringSplit($sNewDate, "/")
         _ArrayAdd($aPublicHolidays, $aNewDate[3] & "/" & $aNewDate[2] & "/" & $aNewDate[1] & " : " & "Monday")

       Case $NewYearsDay = "Sunday"
         $sNewDate = _DateAdd( 'd',1, $year & "/" & 01 & "/" & 01)
         $aNewDate = StringSplit($sNewDate, "/")
         _ArrayAdd($aPublicHolidays, $aNewDate[3] & "/" & $aNewDate[2] & "/" & $aNewDate[1] & " : " & "Monday")

      Case Else
         _ArrayAdd($aPublicHolidays, "01/01/" & $year & " : " & $NewYearsDay)
   EndSelect
   ;--------------------------------------------------------------------

   ;Easter
   Local $aEasterSunday = StringSplit(_DateGetEaster($year), "/")
   Dim $EasterSunday
   If Ubound($aEasterSunday) -1 = 3 Then
      $EasterSunday = _DateDayOfWeek(_DateToDayOfWeek($aEasterSunday[1], $aEasterSunday[2], $aEasterSunday[3]))
      $EasterSunday = $aEasterSunday[3] & "/" & $aEasterSunday[2] & "/" & $aEasterSunday[1]
      Local $EasterSundayDay = _DateDayOfWeek(_DateToDayOfWeek($aEasterSunday[1],$aEasterSunday[2], $aEasterSunday[3] ))
   Else
      $EasterSunday = "?"
      Local $EasterSundayDay = "?"
   EndIf

   ; Work out Good Friday - minus 2 days from Easter Sunday
      $sNewDate = _DateAdd( 'd',-2, $aEasterSunday[1] & "/" & $aEasterSunday[2] & "/" & $aEasterSunday[3])
      $aNewDate = StringSplit( $sNewDate, "/" )
      $GoodFriday =  $aNewDate[3] & "/" &  $aNewDate[2] & "/" &  $aNewDate[1]
      _ArrayAdd($aPublicHolidays, $GoodFriday & " : " & "Friday")

   ; Work out Easter Monday - Plus 1 days from Easter Sunday
      $sNewDate = _DateAdd( 'd',1, $aEasterSunday[1] & "/" & $aEasterSunday[2] & "/" & $aEasterSunday[3])
      $aNewDate = StringSplit( $sNewDate, "/" )
      $EasterMonday =  $aNewDate[3] & "/" &  $aNewDate[2] & "/" &  $aNewDate[1]
      _ArrayAdd($aPublicHolidays, $EasterMonday & " : " & "Monday")
   ;------------------------------------------

   ;Early May Bank Hols - First Monday in May
   Dim $FirstNamedDays[8][2] ;array of [dates][last Weekday in month]
   For $i = 1 To 7
      If _DateDayOfWeek(_DateToDayOfWeek($Year, 5, 0 + $i)) = "Monday" Then
         $FirstNamedDays[$i][0] = 0 & 0 + $i & "/" & 0 & 5 & "/" & $Year
         $FirstNamedDays[$i][1] = "First " & _DateDayOfWeek(_DateToDayOfWeek($Year, 5, 0 + $i))

         _ArrayAdd($aPublicHolidays,$FirstNamedDays[$i][0] & " : " & "Monday")
      EndIf
    Next

   ;---------------------------------------------

   ;Spring Bank Holiday - Last Monday in May
   $DaysInMonth = _DateDaysInMonth($Year, 5) ;how many days the current month has
   $LastDayInMonth = _DateToDayOfWeek($Year, 5, $DaysInMonth) ;last day in the month (date format)

   Dim $LastNamedDays[7][2] ;array of [dates][last Weekday in month]

   For $i = 0 To 6

      If _DateDayOfWeek(_DateToDayOfWeek($Year, 5, $DaysInMonth - $i)) = "Monday" Then
         $LastNamedDays[$i][1] = "Last " & _DateDayOfWeek(_DateToDayOfWeek($Year, 5, $DaysInMonth - $i))
         $LastNamedDays[$i][0] =  _DateDaysInMonth($year, 5) - $i & "/" & 0 & 5 & "/" & $Year

         _ArrayAdd($aPublicHolidays, $LastNamedDays[$i][0] & " : " & "Monday")

      EndIf
   Next

   ;--------------------------------------------------
   ;Summer Bank Holiday - Last Monday in august
   $DaysInMonth = _DateDaysInMonth($Year, 8) ;how many days the current month has
   $LastDayInMonth = _DateToDayOfWeek($Year, 8, $DaysInMonth) ;last day in the month (date format)

   Dim $LastNamedDays[7][2] ;array of [dates][last Weekday in month]

   For $i = 0 To 6

      If _DateDayOfWeek(_DateToDayOfWeek($Year, 8, $DaysInMonth - $i)) = "Monday" Then
         $LastNamedDays[$i][1] = "Last " & _DateDayOfWeek(_DateToDayOfWeek($Year, 8, $DaysInMonth - $i))
         $LastNamedDays[$i][0] =  _DateDaysInMonth($year, 8) - $i & "/" & 0 & 8 & "/" & $Year

         _ArrayAdd($aPublicHolidays,  $LastNamedDays[$i][0] & " : " & "Monday")

      EndIf
   Next

   ;--------------------------------------------
   ;Xmas
   Local $XmasDay = _DateDayOfWeek(_DateToDayOfWeek($year, 12, 25))
   Local $BoxingDay = _DateDayOfWeek(_DateToDayOfWeek($year, 12, 26))

   Dim $AddWeekDayHoliday = 0

   ;Now add the correct Xmas Day bank holiday to the Array
      Select
         Case $XmasDay = "Saturday"
            $sNewDate = _DateAdd( 'd',2, $year & "/" & 12 & "/" & 25)
            $aNewDate = StringSplit($sNewDate, "/")
            _ArrayAdd($aPublicHolidays, $aNewDate[3] & "/" & $aNewDate[2] & "/" & $aNewDate[1] & " : " & "Monday")

         Case $XmasDay = "Sunday"
            $sNewDate = _DateAdd('d',2, $year & "/" & 12 & "/" & 25)
            $aNewDate = StringSplit($sNewDate, "/")
            _ArrayAdd($aPublicHolidays, $aNewDate[3] & "/" & $aNewDate[2] & "/" & $aNewDate[1] & " : " & "Tuesday") ; Skip the Monday as Boxing Day will automatically take this day

         Case Else
            _ArrayAdd($aPublicHolidays, "25" & "/12/" & $year & " : " & $XmasDay)
      EndSelect


   ;Now add the correct Boxing Day bank holiday to the array

      Select
         Case $BoxingDay = "Saturday"
            $sNewDate = _DateAdd( 'd',2, $year & "/" & 12 & "/" & 26)
            $aNewDate = StringSplit($sNewDate, "/")
            _ArrayAdd($aPublicHolidays, $aNewDate[3] & "/" & $aNewDate[2] & "/" & $aNewDate[1] & " : " & "Monday")

         Case $BoxingDay = "Sunday"
            $sNewDate = _DateAdd( 'd',2, $year & "/" & 12 & "/" & 26) ; Need to add 2 days not 1 day as Xmas day is also owed and will have already substituted the Monday for Xmas day
            $aNewDate = StringSplit($sNewDate, "/")
            _ArrayAdd($aPublicHolidays, $aNewDate[3] & "/" & $aNewDate[2] & "/" & $aNewDate[1] & " : " & "Tuesday")

         Case Else
            _ArrayAdd($aPublicHolidays, "26" & "/12/" & $year & " : " & $BoxingDay)

      EndSelect


   ;-------------------------------------------


   Return $aPublicHolidays


EndFunc



Func _DateGetEaster($iYear, $method = 3)

   ;Source for this function : https://www.autoitscript.com/trac/autoit/ticket/1715

    Local $iDay = 0 ; default values for invalid arguments
    Local $iMonth = 0
    $iYear = Int($iYear)

    If $method < 1 Or $method > 3 Then
        Return SetError(1, 0, "") ; Method must be 1, 2 or 3
    ElseIf $method = 1 And $iYear < 326 Then
        Return SetError(2, 0, "") ; The original calculation applies to all years from 326 AD
    ElseIf ($method = 2 Or $method = 3) And ($iYear < 1583 Or $iYear > 4099) Then
        Return SetError(3, 0, "") ; Gregorian calendar Easters apply for years 1583 to 4099 only
    EndIf

    Local $g ; golden year - 1
    Local $c ; century
    Local $h ; = (23 - Epact) mod 30
    Local $i ; no of days from March 21 to Paschal Full Moon
    Local $j ; weekday for PFM (0=Sunday, etc)
    Local $p ; no of days from March 21 to Sunday on or before PFM
    ; (-6 to 28 methods 1 & 3, to 56 for method 2)
    Local $e = 0 ; extra days to add for method 2 (converting Julian date to Gregorian date)
    $c = Int($iYear / 100)
    $g = Mod($iYear, 19)
    If $method = 1 Or $method = 2 Then ; old method (Julian)
        $i = Mod((19 * $g + 15), 30)
        $j = Mod($iYear + Int($iYear / 4) + $i, 7)
        If $method = 2 Then ; extra dates to convert Julian to Gregorian date (Orthodox Church)
            $e = 10
            If $iYear > 1600 Then $e = $e + $c - 16 - Int(($c - 16) / 4)
        EndIf
    ElseIf $method = 3 Then ; new method (Gregorian -> Western Church)
        $h = Mod($c - Int($c / 4) - Int((8 * $c + 13) / 25) + 19 * $g + 15, 30)
        $i = $h - Int($h / 28) * (1 - Int(29 / ($h + 1)) * Int((21 - $g) / 11))
        $j = Mod($iYear + Int($iYear / 4) + $i + 2 - $c + Int($c / 4), 7)
    EndIf
    ; return day and month
    $p = $i - $j + $e

    ; p can be from -6 to 56 corresponding to dates 22 March to 23 May
    ; (later dates apply to method 2, although 23 May never actually occurs)
    $iMonth = 3 + Int(($p + 26) / 30)
    $iDay = 1 + Mod($p + 27 + Int(($p + 6) / 40), 31)

    If $iDay < 10 Then $iDay = String("0" & $iDay)
    Return $iYear & "/0" & $iMonth & "/" & $iDay
EndFunc   ;==>_DateGetEaster

Enjoy