Date difference

dynamitemedia · May 10, 2015

ok i have found a lot of solutions when using datdiff etc but the issue is the excel file is coming back with dates like this:

Date=Fri, 08 May 2015

how can i compare that date to today's date?

i am getting today's date using this:

Func _GetTodaysDate($iReturnTime = 1)
    Local $aMDay[8] = [7, "Sun", "Mon", "Tue", "Wed", "Thur", "Fri", "Sat"], _
            $aMonth[13] = [12, "Jan", "Feb", "Mar", "Apr", "May", "June", "July", "Aug", "Sept", "Oct", "Nov", "Dec"], $aTime[2] = ["", ' ' & @HOUR & ':' & @MIN & ':' & '00']
    Return $aMDay[@WDAY] & ', ' & @MDAY & ' ' & $aMonth[@MON] & ' ' & @YEAR & $aTime[$iReturnTime]
 EndFunc

this will return:
Sun, 10 May 2015

it would be nice to just compare like this:

If $todaysDate < $iniDate then

I have looked at datdiff and stringcompare but that means i have to change the format of dates... which i can do using today's date but cant figure out how to take the
Date=Fri, 08 May 2015 into a all numeric format

any ideas?

Malkey · May 11, 2015

This should work as long as the Excel date format is the same as the $sDateTimeFormat parameter of the '_Date_Time_Convert' function.

Example: "Date=Fri, 08 May 2015" is in the format of "Date=ddd, dd MMMM yyyy".

Being May, the month format could be "MMMM" or "MMM".

#include <Date.au3>

Local $sExcelDate = "Date=Fri, 08 May 2015"
Local $sExcelDateConv = _Date_Time_Convert($sExcelDate, "Date=ddd, dd MMMM yyyy", "yyyy/MM/dd")
Local $sNow = _NowCalcDate() ; Returns today's date in "yyyy/MM/dd" format.

ConsoleWrite("Excel  Date  = " & $sExcelDateConv & @LF) ;
ConsoleWrite("Today's Date = " & $sNow & @LF)

ConsoleWrite("Difference = " & _DateDiff("D", $sExcelDateConv, $sNow) & " days" & @LF)


; http://www.autoitscript.com/forum/topic/112520-date-time-convert/#entry788257
; #FUNCTION# ==================================================================================
; Name...........: _Date_Time_Convert
; Description ...: Converts a date and/or time from its existing format to any other custom format.
; Example Date time "Thursday, 1 April, 2010 @ 08:05:08 PM"
; Example's Format Eg : "dddd, d MMMM, yyyy @ hh:mm:ss tt"
; Year. : yyyy = 2010 ; yy = 10
; Month : MMMM = April; MMM = Apr; MM = 04; M = 4
; Day.. : dddd = Thursday; ddd = Thu; dd = 01; d = 1
; Hour. : HH = 20; H = 20 (1 digit minimum); hh = 08; h = 8 (Lowercase h's used with AM/PM time)
; Minute: mm = 05 (2 digit minimum); m = 5 (1 digit minimum)
; Second: ss = 08 (2 digit minimum); s = 8 (1 digit minimum)
; AM/PM : tt = AM or PM; t = A or P
; Ref: https://msdn.microsoft.com/en-us/library/bb761726%28v=VS.85%29.aspx#dtp_format_chars
; =============================================================================================
Func _Date_Time_Convert($sDateTime, $sDateTimeFormat, $sRetFormat = "yyyy/MM/dd HH:mm:ss")
    Local $Time, $iYear, $iMnth, $iDay, $iHour, $iMinute, $iSec, $aDTFormat, $aDTVal, $iYearLen
    Local $aMMM[13] = [12, "jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec"]

    $aDTFormat = StringRegExp($sDateTimeFormat, "yyyy|yy|MMMM|MMM|MM|M|dddd|ddd|dd|d|HH|H|hh|h|mm|m|ss|s|tt|t|\d+|[[:punct:]]", 3)
    $aDTVal = StringRegExp($sDateTime, "[a-zA-Z]+|\d{1,4}|[[:punct:]]", 3)
    If UBound($aDTFormat) <> UBound($aDTVal) Then
        MsgBox(0, "ERROR", " Possibly need a delineator between the digit values for year, month, day, hour, minute, or second.", 5)
        Return
    EndIf
    For $i = 0 To UBound($aDTFormat) - 1
        Select
            Case $aDTFormat[$i] == "yy" Or $aDTFormat[$i] == "yyyy" ; y - Year
                $iYearLen = StringLen($aDTFormat[$i])
                If $iYearLen = 4 Then $iYear = $aDTVal[$i] & "/"
                If $iYearLen = 2 Then
                    If Number($aDTVal[$i]) < Number(StringRight(@YEAR, 2) + 20) Then
                        $iYear = "20" & $aDTVal[$i] & "/"
                    Else
                        $iYear = "19" & $aDTVal[$i] & "/"
                    EndIf
                EndIf

            Case $aDTFormat[$i] == "M" Or $aDTFormat[$i] == "MM" Or $aDTFormat[$i] == "MMM" Or $aDTFormat[$i] == "MMMM" ; M - Month
                If StringLen($aDTFormat[$i]) > 2 Then
                    For $j = 1 To UBound($aMMM) - 1
                        If StringLeft($aDTVal[$i], 3) = $aMMM[$j] Then $aDTVal[$i] = $j
                    Next
                EndIf
                $iMnth = StringRight("0" & $aDTVal[$i], 2) & "/"

            Case $aDTFormat[$i] == "d" Or $aDTFormat[$i] == "dd" ; d - Day
                $iDay = StringRight("0" & $aDTVal[$i], 2) & " "

            Case $aDTFormat[$i] == "h" Or $aDTFormat[$i] == "hh" ; or StringRegExp( $aDTFormat[$i],"(?i)hh?tt?");  h - Hour
                $iHour = $aDTVal[$i]
                If $iHour = 12 Then $iHour = 0
                For $k = 0 To UBound($aDTFormat) - 1
                    If ($aDTFormat[$k] == "t" Or $aDTFormat[$k] == "tt") And StringLeft($aDTVal[$k], 1) = "p" Then $iHour = Mod(12 + $iHour, 24)
                Next
                $iHour = StringRight("0" & $iHour, 2) & ":"

            Case $aDTFormat[$i] == "H" Or $aDTFormat[$i] == "HH" ;  H - Hour
                $iHour = StringRight("0" & $aDTVal[$i], 2) & ":"

            Case $aDTFormat[$i] == "m" Or $aDTFormat[$i] == "mm" ;or StringRegExp( $aDTFormat[$i],"(?i)mm?tt?") ; m - Minute
                $iMinute = StringRight("0" & $aDTVal[$i], 2) & ":"

            Case $aDTFormat[$i] == "s" Or $aDTFormat[$i] == "ss" ; or StringRegExp( $aDTFormat[$i],"(?i)ss?tt?") ; s - Second
                $iSec = StringRight("0" & $aDTVal[$i], 2) & ":"
        EndSelect
    Next

    ; Default values added to empty, unused variables for entry into the Date Control.
    If $iYear = "" Then $iYear = "1900/"
    If $iMnth = "" Then $iMnth = "01/"
    If $iDay = "" Then $iDay = "01 "
    If $iHour = "" Then $iHour = "00:"
    If $iMinute = "" Then $iMinute = "00:"
    If $iSec = "" Then $iSec = "00"
    $Time = $iYear & $iMnth & $iDay & $iHour & $iMinute & $iSec

    ;===== The following converts $Time to $sRetFormat format using Date Control ======
    ; $Time is now in this format "yyyy/MM/dd HH:mm:ss"
    Local $hGui = GUICreate("My GUI get date", 200, 200, 800, 200)
    Local $idDate = GUICtrlCreateDate($Time, 10, 10, 185, 20)
    GUICtrlSendMsg($idDate, 0x1032, 0, $sRetFormat)
    Local $sReturn = GUICtrlRead($idDate)
    GUIDelete($hGui)
    Return $sReturn
EndFunc   ;==>_Date_Time_Convert

mikell · May 11, 2015

cant figure out how to take the Date=Fri, 08 May 2015 into a all numeric format

This is a simple way to return your date in YYYY/MM/DD format to use with DateDiff()

$mydate = "Date=Fri, 08 May 2015"
Msgbox(0,"", _DateCalc($mydate))

Func _DateCalc($date)
  Local $mon, $tmp = StringSplit($date, " ")
  Local $aMonth[13] = [12, "Jan", "Feb", "Mar", "Apr", "May", "June", "July", "Aug", "Sept", "Oct", "Nov", "Dec"]
  For $i = 1 to 12
     If $aMonth[$i] = $tmp[3] Then $mon = $i
  Next
  Return StringFormat("%4i/%02i/%02i", $tmp[4], $mon, $tmp[2])
EndFunc

SadBunny · May 11, 2015

How sure are you that you will always get the same date format from Excel (i.e. will your script be running on different machines with different locales, languages, date format preferences etc. or is it just for one machine that you control yourself)? And how are you reading it, direct interaction with Excel or from an exported CSV file or something?

Edited May 11, 2015 by SadBunny

dynamitemedia · May 11, 2015

thanks guys will mess with it later tonight actually i missed the $ lol should be

$date = "Fri, 08 May 2015"

will post some code when i get home

dynamitemedia · May 13, 2015

OK switched from excel and went to INI...

I also incorporated code from here: https://www.autoitscript.com/forum/topic/164539-better-way-to-write-a-task-scheduler/

Please tell me if there is a cleaner way of doing this:

runTask()
local $ini = @workingdir & "\config.ini"
local $Tasks,$TaskInfo,$OldIni

while 1
   if FileRead($ini) <> $OldIni then                        ; Check for any updates to the ini file
      Local $sFileRead = FileRead($ini)
      ConsoleWrite( "Ini :  " & $sFileRead  & @CRLF)

      Local $sFileRead = FileRead($OldIni)
      ;ConsoleWrite( "Old INI :  " & $sFileRead  & @CRLF)

      $Tasks = IniReadSectionNames($ini)                    ;Gathers each section name from ini (the identifier used per task in this example)
      $OldIni = FileRead($ini)                              ;Stores Entire contents of Ini to variable (Cheap and ugly update check on the config)

      if IsArray($TaskInfo) then                            ;Avoid ReDim Errors by checking if array exists before declaration
         ReDim $TaskInfo[Ubound($Tasks)][5]
      Else
         Dim $TaskInfo[Ubound($Tasks)][5]
      EndIf

      For $i = 0 to uBound($Tasks) - 1                      ; For Loop to gather all task data for each task into an array
         $TaskInfo[$i][0] = $Tasks[$i]
         $TaskInfo[$i][1] = iniRead($ini,$Tasks[$i],"Location","")
         $TaskInfo[$i][2] = iniRead($ini,$Tasks[$i],"Date","")
         $TaskInfo[$i][3] = iniRead($ini,$Tasks[$i],"Time","")
         $TaskInfo[$i][4] = False                           ;Flag to ensure only run once
      Next
   EndIf

   For $x = 0 to Ubound($TaskInfo,1) - 1                    ;For loop to check if any tasks are to be run.
      Local $todaysDate = _GetTodaysDate(0)
         ;ConsoleWrite( "todaysDate :  " & $todaysDate & @CRLF) ; If you're in SciTE

      Local $iniDate = $TaskInfo[$x][2]
         ;ConsoleWrite( "iniDate :  " & $iniDate & @CRLF) ; If you're in SciTE

      Local $deleteEntry = $TaskInfo[$x][0]
         ;ConsoleWrite( "deleteEntry :  " & $deleteEntry & @CRLF) ; If you're in SciTE

      Local $sTodaysDate = _Date_Time_Convert($iniDate, "ddd, dd MMMM yyyy", "yyyy/MM/dd")
         ;ConsoleWrite( "sTodaysDate :  " & $sTodaysDate & @CRLF) ; If you're in SciTE
      Local $sNow = _Date_Time_Convert($todaysDate, "ddd, dd MMMM yyyy", "yyyy/MM/dd")
         ;ConsoleWrite( "sNow :  " & $sNow & @CRLF) ; If you're in SciTE

      $sDifference = _DateDiff("D", $sNow, $sTodaysDate)
         ;ConsoleWrite( "Days Difference :  " & $sDifference & @CRLF & @CRLF) ; If you're in SciTE

      If $sDifference <= 0 then
         ;ConsoleWrite( "Difference is less than 0, lets delete and is : " & $sDifference  & @CRLF) ; If you're in SciTE
         IniDelete($ini,$deleteEntry)                       ; Now lets delete Entry
      EndIf

      If $sDifference >= 0 then

         If $sDifference == 0 then
            $times = @HOUR & ":" & @MIN
            ;ConsoleWrite( "Difference is less than 0, lets delete and is : " & $sDifference  & @CRLF) ; If you're in SciTE
               if $times = $TaskInfo[$x][3] and $TaskInfo[$x][4] == False Then
                  $task = $TaskInfo[$x][1]
                  ConsoleWrite( "that is today SO lets do something: " & $task  & @CRLF) ; If you're in SciTE

                  Run($task)                                        ; Run the task using the Directory value stored in the config file
                  $TaskInfo[$x][4] = True                       ; Set the run flag to true for this task
                  ;IniDelete($ini,$deleteEntry)                     ; Now lets delete Entry
               EndIf
            EndIf
         EndIf
   Next
WEnd

EndFunc

Func _GetTodaysDate($iReturnTime = 1)
    Local $aMDay[8] = [7, "Sun", "Mon", "Tue", "Wed", "Thur", "Fri", "Sat"], _
            $aMonth[13] = [12, "Jan", "Feb", "Mar", "Apr", "May", "June", "July", "Aug", "Sept", "Oct", "Nov", "Dec"], $aTime[2] = ["", ' ' & @HOUR & ':' & @MIN & ':' & '00']
    Return $aMDay[@WDAY] & ', ' & @MDAY & ' ' & $aMonth[@MON] & ' ' & @YEAR & $aTime[$iReturnTime]
 EndFunc

Func _Date_Time_Convert($sDateTime, $sDateTimeFormat, $sRetFormat = "yyyy/MM/dd HH:mm:ss")
    Local $Time, $iYear, $iMnth, $iDay, $iHour, $iMinute, $iSec, $aDTFormat, $aDTVal, $iYearLen
    Local $aMMM[13] = [12, "jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec"]

    $aDTFormat = StringRegExp($sDateTimeFormat, "yyyy|yy|MMMM|MMM|MM|M|dddd|ddd|dd|d|HH|H|hh|h|mm|m|ss|s|tt|t|\d+|[[:punct:]]", 3)
    $aDTVal = StringRegExp($sDateTime, "[a-zA-Z]+|\d{1,4}|[[:punct:]]", 3)
    If UBound($aDTFormat) <> UBound($aDTVal) Then
        ;MsgBox(0, "ERROR", " Possibly need a delineator between the digit values for year, month, day, hour, minute, or second.", 5)
        Return
    EndIf
    For $i = 0 To UBound($aDTFormat) - 1
        Select
            Case $aDTFormat[$i] == "yy" Or $aDTFormat[$i] == "yyyy" ; y - Year
                $iYearLen = StringLen($aDTFormat[$i])
                If $iYearLen = 4 Then $iYear = $aDTVal[$i] & "/"
                If $iYearLen = 2 Then
                    If Number($aDTVal[$i]) < Number(StringRight(@YEAR, 2) + 20) Then
                        $iYear = "20" & $aDTVal[$i] & "/"
                    Else
                        $iYear = "19" & $aDTVal[$i] & "/"
                    EndIf
                EndIf

            Case $aDTFormat[$i] == "M" Or $aDTFormat[$i] == "MM" Or $aDTFormat[$i] == "MMM" Or $aDTFormat[$i] == "MMMM" ; M - Month
                If StringLen($aDTFormat[$i]) > 2 Then
                    For $j = 1 To UBound($aMMM) - 1
                        If StringLeft($aDTVal[$i], 3) = $aMMM[$j] Then $aDTVal[$i] = $j
                    Next
                EndIf
                $iMnth = StringRight("0" & $aDTVal[$i], 2) & "/"

            Case $aDTFormat[$i] == "d" Or $aDTFormat[$i] == "dd" ; d - Day
                $iDay = StringRight("0" & $aDTVal[$i], 2) & " "

            Case $aDTFormat[$i] == "h" Or $aDTFormat[$i] == "hh" ; or StringRegExp( $aDTFormat[$i],"(?i)hh?tt?");  h - Hour
                $iHour = $aDTVal[$i]
                If $iHour = 12 Then $iHour = 0
                For $k = 0 To UBound($aDTFormat) - 1
                    If ($aDTFormat[$k] == "t" Or $aDTFormat[$k] == "tt") And StringLeft($aDTVal[$k], 1) = "p" Then $iHour = Mod(12 + $iHour, 24)
                Next
                $iHour = StringRight("0" & $iHour, 2) & ":"

            Case $aDTFormat[$i] == "H" Or $aDTFormat[$i] == "HH" ;  H - Hour
                $iHour = StringRight("0" & $aDTVal[$i], 2) & ":"

            Case $aDTFormat[$i] == "m" Or $aDTFormat[$i] == "mm" ;or StringRegExp( $aDTFormat[$i],"(?i)mm?tt?") ; m - Minute
                $iMinute = StringRight("0" & $aDTVal[$i], 2) & ":"

            Case $aDTFormat[$i] == "s" Or $aDTFormat[$i] == "ss" ; or StringRegExp( $aDTFormat[$i],"(?i)ss?tt?") ; s - Second
                $iSec = StringRight("0" & $aDTVal[$i], 2) & ":"
        EndSelect
    Next



;#cs


    ; Default values added to empty, unused variables for entry into the Date Control.
    If $iYear = "" Then $iYear = "1900/"
    If $iMnth = "" Then $iMnth = "01/"
    If $iDay = "" Then $iDay = "01 "
    If $iHour = "" Then $iHour = "00:"
    If $iMinute = "" Then $iMinute = "00:"
    If $iSec = "" Then $iSec = "00"
    $Time = $iYear & $iMnth & $iDay & $iHour & $iMinute & $iSec




    ;===== The following converts $Time to $sRetFormat format using Date Control ======
    ; $Time is now in this format "yyyy/MM/dd HH:mm:ss"
    Local $hGui = GUICreate("My GUI get date", 200, 200, 800, 200)
    Local $idDate = GUICtrlCreateDate($Time, 10, 10, 185, 20)
    GUICtrlSendMsg($idDate, 0x1032, 0, $sRetFormat)
    Local $sReturn = GUICtrlRead($idDate)
    GUIDelete($hGui)
    Return $sReturn

;#ce


EndFunc   ;==>_Date_Time_Convert

EDIT: One thing i may add after deleting the ini entry is deleting any empty lines at the top just to tidy up a bit...

Edited May 13, 2015 by dynamitemedia
tidy up idea

Sign In

Date difference

Recommended Posts

dynamitemedia

Malkey

mikell

SadBunny

dynamitemedia

dynamitemedia

Create an account or sign in to comment

Create an account

Sign in

Similar Content

Free style DateTimeFormat

ListView sorting on date

simple date calculator

How to fetch date/time of last windows 10 system restore point?

GUICtrlSetData fails to set time in a "Date" control

Browse

AutoIt Resources

Release

Beta