Pre 1900 day date calculations

[huge]

Hello folks,

I am looking for help to work with pre 1900 dates by adding days.
As an example i have a date of 1 Jan 1788 and would like to add (x) days to it working toward 1 Jan 1900 in multiples of (x) days when Excel starts to work.

7 Feb 1788 plus (50 days) = 28 March 1788.
28 March 1788 plus (50 days) = 16 May 1788.
16 May 1788 plus (50 days) = 4 July 1788.
etc, etc

Thank you
Huge

 

[Dryver14]

place these functions into a module then type 7 feb 1788 into a cell next cell down type =Xdateadd(select cell above, 50) this will return the short date for your computer if you want a different format put another comma after the 50 and "dd mm yyyy"
or what ever format you want then drag down.

Function XDATEADD(xdate1, days, Optional fmt As String) As String
    Dim TempDate As Date
    If IsMissing(fmt) Then fmt = "Short Date"
    xdate1 = RemoveDay(xdate1)
    TempDate = DateValue(xdate1)
    XDATEADD = Format(TempDate + days, fmt)
End Function


Private Function RemoveDay(xdate1)
'   Remove day of week from string
    Dim i As Integer
    Dim Temp As String
    Temp = xdate1
    For i = 0 To 6 'Unabbreviated day names
        Temp = Application.Substitute(Temp, Format(DateSerial(1900, 1, 0), "dddd"), "")
    Next i
    For i = 0 To 6 'Abbreviated day names
        Temp = Application.Substitute(Temp, Format(DateSerial(1900, 1, 0), "ddd"), "")
    Next i
    RemoveDay = Temp
End Function

 

[Taul]

Hi,
There is a good article on using dates prior to 1900, link below and also a downloadable file you can play with.

http://www.exceluser.com/formulas/earlydates.htm

hope this helps
Paul.

 

[huge]

John thank you very much, most appreciated.
Huge

 

[huge]

Paul fabulous and thank you that helps me greatly.
Huge