Excel sequential serial number To DATE

[DXRW4E]

_DateToSSN Fuction return sequential serial number that represent a particular Date, and _SSNToDate Fuction return Date from sequential serial number (generated from DATE fuction on EXCEL or _DateToSSN Fuction, example 39637 = 7/8/2008), as already look at the function and too simple and uses few line of code, so that it is very fast (_DateToSSN processes about 55000 per second & _SSNToDate processes about 20000 per second)

expandcollapse popup

Global $DateSSN[27] = [0, 31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0, 31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335]

ConsoleWrite("7/8/2008  - 39637   =   " & _SSNToDate(39637) & " - " & _DateToSSN(2008, 7, 8) & @LF)
ConsoleWrite("6/1/1998  - 35947   =   " & _SSNToDate(35947) & " - " & _DateToSSN(1998, 6, 1) & @LF)
ConsoleWrite("1/1/2000  - 36526   =   " & _SSNToDate(36526) & " - " & _DateToSSN(2000, 1, 1) & @LF)
ConsoleWrite("10/9/2010 - 40460   =   " & _SSNToDate(40460) & " - " & _DateToSSN(2010, 10, 9) & @LF)
ConsoleWrite("10/9/2011 - 40825   =   " & _SSNToDate(40825) & " - " & _DateToSSN(2011, 10, 9) & @LF)
ConsoleWrite("10/9/2012 - 41191   =   " & _SSNToDate(41191) & " - " & _DateToSSN(2012, 10, 9) & @LF)
ConsoleWrite("10/9/2013 - 41556   =   " & _SSNToDate(41556) & " - " & _DateToSSN(2013, 10, 9) & @LF)
ConsoleWrite("10/9/2015 - 42286   =   " & _SSNToDate(42286) & " - " & _DateToSSN(2015, 10, 9) & @LF)
ConsoleWrite("10/9/2014 - 41921   =   " & _SSNToDate(41921) & " - " & _DateToSSN(2014, 10, 9) & @LF)

; #FUNCTION# =================================================================================================================
; Name...........: _SSNToDate
; Description ...: The _SSNToDate Fuction return Date from sequential serial number
; Syntax.........: _SSNToDate($iDay)
; Parameters ....: $iDay - sequential serial number (generated from DATE fuction on EXCEL, example 39637 = 7/8/2008)
; Return values .: Success  - Return DATE
;                 Failure - @Error
; Author ........: DXRW4E
; Modified.......:
; Remarks .......: DATE String is Month/Day/Year, Year - is number is 1900 to 9999
; Related .......:
; Link ..........:
; Example .......: _SSNToDate(39637)
; Note ..........:
; ============================================================================================================================
Func _SSNToDate($iDay)
    If $iDay < 1 Or $iDay > 2958465 Then Return SetError(1, 0, "")
    $DateSSN[0] = Int($iDay / 365)
    $DateSSN[25] = $DateSSN[0] / 4
    $DateSSN[26] = IsFloat($DateSSN[25])
    $iDay = $iDay - ($DateSSN[0] * 365) - Int($DateSSN[25]) - $DateSSN[26]
    If $iDay < 1 Then
        $DateSSN[0] -= 1
        $DateSSN[25] = IsInt(($DateSSN[0] -1) / 4)
        $iDay += 365 + $DateSSN[25]
        $DateSSN[26] = Int($DateSSN[25] = 0)
    EndIf
    $DateSSN[2] -= $DateSSN[26]
    For $iMonth = 1 To 11
        If $DateSSN[$iMonth] >= $iDay Then ExitLoop
        $iDay -= $DateSSN[$iMonth]
    Next
    $DateSSN[2] += $DateSSN[26]
    Return $iMonth & "/" & $iDay & "/" & (1900 + $DateSSN[0])
EndFunc

; #FUNCTION# =================================================================================================================
; Name...........: _DateToSSN
; Description ...: The _DateToSSN Fuction return sequential serial number that represent a particular Date
; Syntax.........: _DateToSSN($iYear, $iMonth, $iDay)
; Parameters ....: $iYear  - Year  - is number is 1900 to 9999
;                     Required. The value of the year argument can include one to four digits. Excel interprets the year argument
;                     according to the date system your computer is using. By default, Microsoft Excel for Windows uses the 1900 date system.
;                     We recommend using four digits for the year argument to prevent unwanted results. For example, "07" could mean "1907" or "2007." Four digit years prevent confusion.
;                     If year is between 0 (zero) and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE(108,1,2) returns January 2, 2008 (1900+108).
;                     If year is between 1900 and 9999 (inclusive), Excel uses that value as the year. For example, _DateToSSN((2008,1,2) returns January 2, 2008.
;                     If year is less than 0 or is 10000 or greater, _DateToSSN returns the @Error
;                 $iMonth - is number is 1 to 12, If Month is less than 0 or is 13 or greater, _DateToSSN returns the @Error
;                 $iDay   - Required. A positive or negative integer representing the day of the month from 1 to 31.
;                     If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month.
;                       For example, _DateToSSN(2008,1,35) returns the serial number representing February 4, 2008.
;                     If day is less than 1, day subtracts the magnitude that number of days, plus one, from the first day of the month specified.
;                       For example, _DateToSSN(2008,1,-15) returns the serial number representing December 16, 2007.
; Return values .: Success  - Return Sequential Serial Number
;                 Failure - @Error
; Author ........: DXRW4E
; Modified.......:
; Remarks .......: Sequential Serial Number, _DateToSSN(2008, 7, 8) Return 39637, that represent 7/8/2008
;
;                   NOTE - Excel stores dates as sequential serial numbers so that they can be used in calculations. January 1, 1900 is serial number 1,
;                     and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.
;
;                   _DateToSSN NOT SUPPORT FOR NOW, THIS
;                     $iMonth  Required. A positive or negative integer representing the month of the year from 1 to 12 (January to December).
;                       If month is greater than 12, month adds that number of months to the first month in the year specified. For example,
;                       DATE(2008,14,2) returns the serial number representing February 2, 2009.
;                     If month is less than 1, month subtracts the magnitude of that number of months, plus 1, from the first month in the
;                       year specified. For example, DATE(2008,-3,2) returns the serial number representing September 2, 2007.
; Related .......:
; Link ..........:
; Example .......: _DateToSSN(39637)
; Note ..........:
; ============================================================================================================================
Func _DateToSSN($iYear, $iMonth, $iDay)
    If $iYear < 1900 Or $iYear > 9999 Or $iMonth < 1 Or $iMonth > 12 Then Return SetError(1, 0, "")
    $iYear -= 1900
    $DateSSN[0] = $iYear / 4
    If IsFloat($DateSSN[0]) And $iMonth < 3 Then $iDay += 1
    Return ($iYear * 365) + Int($DateSSN[0]) + $DateSSN[$iMonth + 12] + $iDay
EndFunc

Ciao.

Edited October 14, 2012 by DXRW4E

[DXRW4E]

Updated, added _DateToSSN Fuction

Ciao.

[nicdev007]

Hi DXRW4E,

I was about to write a function to do this when I stumbled onto yours! It works great! Just wanted to shout out a thank you for the code :-)

Ciao