Jump to content
Sign in to follow this  
DXRW4E

Excel sequential serial number To DATE

Recommended Posts

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)

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 by DXRW4E

apps-odrive.pngdrive_app_badge.png box-logo.png new_logo.png MEGA_Logo.png

Share this post


Link to post
Share on other sites
DXRW4E

Updated, added _DateToSSN Fuction

Ciao.


apps-odrive.pngdrive_app_badge.png box-logo.png new_logo.png MEGA_Logo.png

Share this post


Link to post
Share on other sites
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

 

 


Life is too short to worry about the things you don't have or cannot do ... So if you don't know how to do it - Learn it! Don't be afraid to ask for help ...

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
Sign in to follow this  

×