If I may, I might like to make one last mod to the last macros, this will make sure you have always 10 characters:
Code: Select all
Sub Beautifully()
Let Range("F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "").NumberFormat = "@"
Let Range("F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "").Value = Evaluate("=DAY(ROW(1:" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "))" & "&" & """/""" & "&" & "MONTH(" & Range("A1").Value2 & ")" & "&""/""&" & "YEAR(" & Range("A1").Value2 & ")")
Let Range("F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "").Value = Evaluate("=If({1},REPLACE(""0000000000"",11-LEN(F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "),LEN(F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "),F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "))")
End Sub
Sub BeautifullyDone()
With Range("F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "")
Let .NumberFormat = "@"
Let .Value = Evaluate("=DAY(ROW(1:" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "))" & "&" & """/""" & "&" & "MONTH(" & Range("A1").Value2 & ")" & "&""/""&" & "YEAR(" & Range("A1").Value2 & ")")
Let .Value = Evaluate("=If({1},REPLACE(""0000000000"",11-LEN(F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "),LEN(F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "),F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "))")
End With
End Sub
_.__________________________________________________________________________________________________________________________________________________________________________________________________________________________-
YasserKhalil wrote: ↑05 Nov 2021, 19:18
you have formatted the output as text and not as dates (and I need the real dates). Is there an easy way to convert the dates to real dates?
As for the final date format. I am not sure …_
_....Maybe this will work
Code: Select all
Sub BeautifullyDoneAgain()
With Range("F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "")
Let .NumberFormat = "@"
Let .Value = Evaluate("=DAY(ROW(1:" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "))" & "&" & """/""" & "&" & "MONTH(" & Range("A1").Value2 & ")" & "&""/""&" & "YEAR(" & Range("A1").Value2 & ")")
Let .Value = Evaluate("=If({1},REPLACE(""0000000000"",11-LEN(F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "),LEN(F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "),F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "))")
Let .NumberFormat = "m/d/yyyy"
End With
End Sub
or this , if you prefer
Code: Select all
Sub PrettyBeautiful()
Let Range("F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "").NumberFormat = "@"
Let Range("F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "").Value = Evaluate("=DAY(ROW(1:" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "))" & "&" & """/""" & "&" & "MONTH(" & Range("A1").Value2 & ")" & "&""/""&" & "YEAR(" & Range("A1").Value2 & ")")
Let Range("F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "").Value = Evaluate("=If({1},REPLACE(""0000000000"",11-LEN(F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "),LEN(F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "),F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "))")
Let Range("F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "").NumberFormat = "m/d/yyyy"
End Sub
I should just say that the last date format ,
"m/d/yyyy" , I got from the macro recorder, it is like in Hans code, it is what the recorder gives me when I select Short date format, (
https://i.postimg.cc/5trDr4BY/Short-Date-Format.jpg ).
Code: Select all
Sub Macro4() ' Got from macro recorder
'
' Macro4 Macro
'
Range("F1:F30").Select
Selection.NumberFormat = "m/d/yyyy" ' - got by selecting Short Date when running macro recorder https://i.postimg.cc/5trDr4BY/Short-Date-Format.jpg
End Sub
- I am slightly confused that I don't have
"d/m/yyyy" or
"dd/mm/yyyy"
If I change to
Let Range("F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "").NumberFormat = "dd/mm/yyyy" , then my Excel tells me that I have format
TT-MM-JJJJ (
https://i.postimg.cc/GtTLfgTr/TT-MM-JJJJ.jpg )
So as ever, date formats are confusing
You may want to experiment with that last format code line a bit….
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also