Convert# of seconds since a date that is before 1990?

[im2bz2p345]

Hi,

Is there a way to convert a text field that is formatted in seconds from the date of 1/1/1841 to mm-dd-yyyy h:mm:ss AM/PM?

For example, in cell F6, I have the text string 5476694198, which after conversion should output to Jul 19, 2014 3:56 PM

I found this article which discussing working with dates before 1/1/1990 in Excel: Pre-1900 Dates in Excel

Since the XDATE add-in created by John Walkenbach seems to be inaccessible now, I looked at the macro referenced (How to calculate ages before 1/1/1900 in Excel - Office), but is there a way to hardcode the end date and uses a formula that only references?

I'm using the latest Excel 365, 32-bit version if that makes a difference.

Regards,

~ im2bz2p345

 

[im2bz2p345]

CORRECTION on first sentence: Is there a way to convert a text field string that displays the number of seconds from the date 1/1/1841 to a format such as mm-dd-yyyy h:mm:ss AM/PM?

~ im2bz2p345

 

[Dave Patton]

How did you calculate Jul 19 2014 ?
Consider the following

Table.xlsm
	B	C	D	E	F
8	5476694198	31,557,600	173.546	Years
9			59	Years to Jan 1 1900
10			114.546
11	01-01-00	114	01-01-14
12		0.5459667	19-Jul-14
1c

Cell Formulas
Range	Formula
C8	C8	=365.25*24*60*60
D8	D8	=B8/C8
D10	D10	=D8-D9
B11	B11	=DATE(1900,1,1)
C11	C11	=INT(D10)
D11	D11	=DATE(1900+C11,1,1)
C12	C12	=MOD(D10,1)
D12	D12	=C12*365+D11

 

[JGordon11]

Here's a UDF that ignores leap seconds (which were introduced in 1972). So for seconds elapsed that will end up later than 1972 this will overstate the time by a few seconds (less than 30). It uses a less than efficient algorithm that runs through each day updating the month, day, and year as it goes. For a one-time calculation it is almost immediate (.01 seconds or so) but if you are running many of these calculations at the same time, I wouldn't recommend using this.

Function DateSeconds(FromYear As Integer, FromMonth As Integer, FromDay As Integer, seconds As Double) As String
    Dim Days As Double, i As Long, monthdays, months, dt As String, tm As String, t As Double
    t = Timer
    monthdays = Array(0, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 30, 31)
    months = Array("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    Days = seconds / 24 / 3600
    For i = 1 To Int(Days)
        FromDay = FromDay + 1
        If FromDay > monthdays(FromMonth) Then
            If FromMonth <> 2 Or FromYear Mod 4 <> 0 Then
                FromDay = 1
                FromMonth = FromMonth + 1
                If FromMonth = 13 Then
                    FromMonth = 1
                    FromYear = FromYear + 1
                End If
            Else
                If FromDay = 30 Then
                    FromDay = 1
                    FromMonth = 3
                End If
            End If
        End If

    Next
    dt = months(FromMonth) & " " & FromDay & ", " & FromYear
    tm = Format(Days - Int(Days), "Long Time")
    DateSeconds = dt & " " & tm
    Debug.Print Timer - t
End Function

=DateSeconds(1841,1,1,5476694198) => Jul 19, 2014 3:56:38 PM

 

[im2bz2p345]

Thank you BOTH for the replies.

Small correction. The start date is actually 12/31/1840 at midnight. See below for a detailed breakdown.

How did you calculate Jul 19 2014 ?
Consider the following...

Hi @Dave Patton - thanks for the reply, but unfortunately that won't work unless all it can be combined into one massive formula without using extra cells. I have a small sample export of data out of a system that using.

Here is how it is calculated manually:

RMR Transition Utility Export POC 1-4-2022.xlsx
	F	G	H
6	5476694198	# days = Total Seconds since 12/31/1840 (F6)/# of seconds in a day (86,400)	63387.66433
7		# of hours = 24 hours in a day X days in the remainder (.66433)	15.94392
8		# of minutes = 60 minutes in a hour X hours in the remainder (0.94392)	56.6352
9		# of seconds = 60 seconds in a minutes X minutes in the remainder (0.6352)	38.112
HRX export

Cell Formulas
Range	Formula
H6	H6	=F6/86400
H7	H7	=24*0.66433
H8	H8	=60*0.94392
H9	H9	=60*0.6352

Values in H6, H7, H8, and H9 were plugged into this website: Date Calculator: Add to or Subtract From a Date – Results

Screenshot:

Thanks,

~ im2bz2p345

 

[im2bz2p345]

Here's a UDF that ignores leap seconds (which were introduced in 1972). So for seconds elapsed that will end up later than 1972 this will overstate the time by a few seconds (less than 30). It uses a less than efficient algorithm that runs through each day updating the month, day, and year as it goes. For a one-time calculation it is almost immediate (.01 seconds or so) but if you are running many of these calculations at the same time, I wouldn't recommend using this.

Hi @JGordon11. This is incredible and exactly what I was after. Thank you for putting it together.

I spot checked using your UDF with many other values and it outputted the correct value each time. I also copy-pasted it to several lines (35) and it almost instantly calculated, so it's definitely operating fairly efficiently.

I wanted to thank you for your efforts. I will wait a day for any other feedback, but will likely mark your response as the solution soon.

~ im2bz2p345

 

[JGordon11]

Thought about this and found the VBA Dateserial function can handle this. Interestingly it returned an answer one day later than the function I provided earlier. It is right and my previous function is off by a day. Reason is that years that are divisible by 100 but not by 400 have no leap day. So, for example, 1800 & 1900 had no leap day but 2000 did. So here is a UDF that is much shorter, faster and best of all it is correct for the century years leap day complication. It presumably works for years 100 to 9999.

Function DateSeconds(FromYear As Integer, FromMonth As Integer, FromDay As Integer, seconds As Double) As String
    Dim dt As Double
    dt = DateSerial(FromYear, FromMonth, FromDay) + seconds / 24 / 3600
    DateSeconds = Format(dt, "mmm d, yyyy") & " " & Format(dt, "Long Time")
End Function

 

[im2bz2p345]

Thought about this and found the VBA Dateserial function can handle this. Interestingly it returned an answer one day later than the function I provided earlier. It is right and my previous function is off by a day. Reason is that years that are divisible by 100 but not by 400 have no leap day. So, for example, 1800 & 1900 had no leap day but 2000 did. So here is a UDF that is much shorter, faster and best of all it is correct for the century years leap day complication. It presumably works for years 100 to 9999.

Function DateSeconds(FromYear As Integer, FromMonth As Integer, FromDay As Integer, seconds As Double) As String
    Dim dt As Double
    dt = DateSerial(FromYear, FromMonth, FromDay) + seconds / 24 / 3600
    DateSeconds = Format(dt, "mmm d, yyyy") & " " & Format(dt, "Long Time")
End Function

Thank for the follow-up, research you did, and the improvements you made @JGordon11!

It works perfectly if I use the start date of midnight on 12/31/1840 as mentioned at the top of post #5, which is the correct starting date timestamp - unfortunately I could not update my original post, so apologies if any confusion was caused.

Another example that I tested out using the string 5460625709. =DateSeconds(1840,12,31,5460625709) => Jan 14, 2014 4:28:29 PM, which is correct.

The updated UDF does seem to operate/compute much more efficiently, as you mentioned.

I hope to use this UDF code with the function =DateSeconds(1840,12,31,<insert cell reference to # of seconds string>) going forward in all of spreadsheets. Once again, I really appreciate your efforts.

~ im2bz2p345