EXCEL pre 1900 date problem: Does anyone have a formula to drag down an EXCEL column "Sunday, January 1, 1900" in descending order?

[The Chocolate Cake Guy]

Does anyone have a formula to drag down an EXCEL column "Sunday, January 1, 1900" in descending order? As most know, one cannot format cells with "Number" "Date Category" prior to 1900. I am trying to list pre 1900 dates in a column in descending order like the following:
Monday, January 2, 1900
Sunday, January 1, 1900
Saturday, December 31, 1899
Friday, December 30, 1899
Thursday, December 29, 1899

Thanks in advance for any help on the matter,
Cake Guy

 

[JoeMo]

I don't have a formula for you, but if all you want is " to list pre 1900 dates in a column in descending order " you can try the macro below. According to several sources I found, 1/2/1900 was a Tuesday. If that's correct then the macro below lists long dates starting with 1/2/1900 and descending at 1 day intervals starting at A1 of the active sheet.

Sub Pre1900Dates()
Const HowManyDays As Long = 20 ' how many days of descending dates to list
Dim Arr() As String, Dte As Variant
Dte = #1/2/1900#       'change start date to suit
Application.ScreenUpdating = False
Range("A:A").ClearContents
ReDim Arr(1 To HowManyDays)
'Arr(1) = CStr(Format(Dte, "[$-x-sysdate]dddd, mmmm dd, yyyy"))
For i = 1 To UBound(Arr)
    Arr(i) = CStr(Format(DateAdd("d", -i + 1, Dte), "[$-x-sysdate]dddd, mmmm dd, yyyy"))
Next i
With Range("A1:A" & HowManyDays)
    .Value = Application.Transpose(Arr)
    .EntireColumn.AutoFit
End With
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Just so you know, January 1, 1900 was actually a Monday, not Sunday. Excel incorrectly considers 1900 to be a Leap Year... it wasn't. Given that, do you want the actual day names or the one's Excel reports even though it is not the correct day name? If I am not mistaken, everything straightens out on March 1, 1900 (before that date, the day names are off by one).

 

[The Chocolate Cake Guy]

I don't have a formula for you, but if all you want is " to list pre 1900 dates in a column in descending order " you can try the macro below. According to several sources I found, 1/2/1900 was a Tuesday. If that's correct then the macro below lists long dates starting with 1/2/1900 and descending at 1 day intervals starting at A1 of the active sheet.

Sub Pre1900Dates()
Const HowManyDays As Long = 20 ' how many days of descending dates to list
Dim Arr() As String, Dte As Variant
Dte = #1/2/1900#       'change start date to suit
Application.ScreenUpdating = False
Range("A:A").ClearContents
ReDim Arr(1 To HowManyDays)
'Arr(1) = CStr(Format(Dte, "[$-x-sysdate]dddd, mmmm dd, yyyy"))
For i = 1 To UBound(Arr)
    Arr(i) = CStr(Format(DateAdd("d", -i + 1, Dte), "[$-x-sysdate]dddd, mmmm dd, yyyy"))
Next i
With Range("A1:A" & HowManyDays)
    .Value = Application.Transpose(Arr)
    .EntireColumn.AutoFit
End With
Application.ScreenUpdating = True
End Sub

Thanks JoeMo for the macro, I'll have to play with it and give it a try.

 

[The Chocolate Cake Guy]

Just so you know, January 1, 1900 was actually a Monday, not Sunday. Excel incorrectly considers 1900 to be a Leap Year... it wasn't. Given that, do you want the actual day names or the one's Excel reports even though it is not the correct day name? If I am not mistaken, everything straightens out on March 1, 1900 (before that date, the day names are off by one).

Thanks Rick for the tip on the year 1900 incorrect days of the week (partial year) due to a non-leap year. I will have to go in there and straighten that out.

 

[Dave Patton]

I do not know what you intend to do with the "dates".
If text information is sufficient, consider some modification of the following.

Enter Dates in Column A
Use Text Formula in Column B
Convert to Values
Use Find and Replace change 1905 to 1899
N.B. I left the formulas in row 2

T202010a.xlsm
	A	B
2	Mon 01 Jan 1906	Mon Jan 01, 1906
3	Sun 31 Dec 1905	Sun Dec 31, 1899
4	Sat 30 Dec 1905	Sat Dec 30, 1899
5	Fri 29 Dec 1905	Fri Dec 29, 1899
6	Thu 28 Dec 1905	Thu Dec 28, 1899
7	Wed 27 Dec 1905	Wed Dec 27, 1899
8	Tue 26 Dec 1905	Tue Dec 26, 1899
9	Mon 25 Dec 1905	Mon Dec 25, 1899
10	Sun 24 Dec 1905	Sun Dec 24, 1899
11	Sat 23 Dec 1905	Sat Dec 23, 1899
12	Fri 22 Dec 1905	Fri Dec 22, 1899
13	Thu 21 Dec 1905	Thu Dec 21, 1899
14	Wed 20 Dec 1905	Wed Dec 20, 1899
15	Tue 19 Dec 1905	Tue Dec 19, 1899
16	Mon 18 Dec 1905	Mon Dec 18, 1899
17	Sun 17 Dec 1905	Sun Dec 17, 1899
18	Sat 16 Dec 1905	Sat Dec 16, 1899
19	Fri 15 Dec 1905	Fri Dec 15, 1899
20	Thu 14 Dec 1905	Thu Dec 14, 1899
2e

Cell Formulas
Range	Formula
B2	B2	=TEXT(A2,"ddd mmm dd, yyy")
A3	A3	=A2-1

 

[The Chocolate Cake Guy]

I do not know what you intend to do with the "dates".
If text information is sufficient, consider some modification of the following.

Enter Dates in Column A
Use Text Formula in Column B
Convert to Values
Use Find and Replace change 1905 to 1899
N.B. I left the formulas in row 2

T202010a.xlsm
	A	B
2	Mon 01 Jan 1906	Mon Jan 01, 1906
3	Sun 31 Dec 1905	Sun Dec 31, 1899
4	Sat 30 Dec 1905	Sat Dec 30, 1899
5	Fri 29 Dec 1905	Fri Dec 29, 1899
6	Thu 28 Dec 1905	Thu Dec 28, 1899
7	Wed 27 Dec 1905	Wed Dec 27, 1899
8	Tue 26 Dec 1905	Tue Dec 26, 1899
9	Mon 25 Dec 1905	Mon Dec 25, 1899
10	Sun 24 Dec 1905	Sun Dec 24, 1899
11	Sat 23 Dec 1905	Sat Dec 23, 1899
12	Fri 22 Dec 1905	Fri Dec 22, 1899
13	Thu 21 Dec 1905	Thu Dec 21, 1899
14	Wed 20 Dec 1905	Wed Dec 20, 1899
15	Tue 19 Dec 1905	Tue Dec 19, 1899
16	Mon 18 Dec 1905	Mon Dec 18, 1899
17	Sun 17 Dec 1905	Sun Dec 17, 1899
18	Sat 16 Dec 1905	Sat Dec 16, 1899
19	Fri 15 Dec 1905	Fri Dec 15, 1899
20	Thu 14 Dec 1905	Thu Dec 14, 1899
2e

Cell Formulas
Range	Formula
B2	B2	=TEXT(A2,"ddd mmm dd, yyy")
A3	A3	=A2-1

 

[The Chocolate Cake Guy]

I originally did something similar in my spreadsheet, Dave. I typed in the seven days of the week and dragged it down and typed in the month in another column and dragged it down and finally dragged in the date and dragged it down. And then I checked it against the online "Calendar Home" program to make minor adjustments for leap years etc. This date information that I have in column A is just a reference to the other cells in the columns to the left. The other column/cells don't need to be physically tied to the date via formulas or agents or anything like that. I think I am good to go now, with the information that I have at this time. I am able to make it work for what I am trying to do. So thanks again to all of you for your help and suggestions.
Cake Guy