Fix dates when populating on worksheet
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Re: Fix dates when populating on worksheet
Thanks again. You have put more working variations but all the output es treated as text no as a date. I just need one macro that treat the output as dates.
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Fix dates when populating on worksheet
YasserKhalil wrote: ↑05 Nov 2021, 20:02all the output es treated as text no as a date. I just need one macro that treat the output as dates.
I don't understand that??
In my Excel, my last macros give me finally date format
This gives me Date
Code: Select all
Sub PrettyBeautiful_Vip()
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
_.____________________________________________________________________________________________________
This gives me Custom , but some sort of Date , I think
Code: Select all
Sub PrettyBeautiful_Vip2()
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 = "dd/mm/yyyy"
End Sub
_.________________________________________________________________________________
I do not know for sure what the best final single macro is. I would probably use one of those last two.
But if I was you, I would experimant a bit with the last code line in your Excel
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Re: Fix dates when populating on worksheet
Thank you very much for pointing me out to the output as appeared on your side. The format appears in Home tab as date. I can extract the month and the day and the year from it.
The only thing that is not OK is the format. When I right-click column F and format the date to make the month full "mmm", this doesn't respond and I still got the same format.
The only thing that is not OK is the format. When I right-click column F and format the date to make the month full "mmm", this doesn't respond and I still got the same format.
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Fix dates when populating on worksheet
Sorry, I don't understand what you are saying?
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Fix dates when populating on worksheet
Alan, look at column F in the screenshot in your next to last post. It is left-aligned but the Alignment group of the Home tab of the ribbon shows that horizontal alignment has not been specified. So the values are text, not dates.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Re: Fix dates when populating on worksheet
I tested the output in column F after executing that code
In the worksheet, I used the functions "Day" and "Month" and "Year" and all is working well
I also looked at the Home tab as you mentioned in your images and found the format of column F is Date (this is OK too)
** The problem now is with formatting the dates using Format Cells command When applying any format like that, it doesn't work
Code: Select all
Sub PrettyBeautiful_Vip()
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 also looked at the Home tab as you mentioned in your images and found the format of column F is Date (this is OK too)
** The problem now is with formatting the dates using Format Cells command When applying any format like that, it doesn't work
You do not have the required permissions to view the files attached to this post.
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Fix dates when populating on worksheet
OK, thanks, I see now what Yassser means now.
Your ( Hans) macro right alligns and that thing with the formatting after right click works.
https://i.postimg.cc/brv8nyjm/Hans-Right-Click.jpg
https://i.postimg.cc/VvHwBxfm/Hans-Right-Click.jpg
My macro left aligns and that thing with the formatting after right click does not work , as Yasser says
So I see what the problem is
I have no idea, (yet), what is going on there
Your ( Hans) macro does not give me the format Yasser wants, but I am very confused with the whole Date subject
So why is my excel telling me I have Date format, but that left justifying is telling me it isn't
?? I am very confused.
There is a solution here
https://www.youtube.com/watch?v=Ya10z2gfRB8
But doing that changes/ messes up the format
Your ( Hans) macro right alligns and that thing with the formatting after right click works.
https://i.postimg.cc/brv8nyjm/Hans-Right-Click.jpg
https://i.postimg.cc/VvHwBxfm/Hans-Right-Click.jpg
My macro left aligns and that thing with the formatting after right click does not work , as Yasser says
So I see what the problem is
I have no idea, (yet), what is going on there
Your ( Hans) macro does not give me the format Yasser wants, but I am very confused with the whole Date subject
So why is my excel telling me I have Date format, but that left justifying is telling me it isn't
?? I am very confused.
There is a solution here
https://www.youtube.com/watch?v=Ya10z2gfRB8
But doing that changes/ messes up the format
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Fix dates when populating on worksheet
You first set the number format to Text ("@").
The you set the values, which will be text values because of that format.
Changing the number format to a date format afterwards doesn't change the values.
The you set the values, which will be text values because of that format.
Changing the number format to a date format afterwards doesn't change the values.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Fix dates when populating on worksheet
I can see that, but I don't really understand why. Excel is contradicting: The left align tells me i'ts text, but the ribon enty above tells me it's Date
Oh well, I don't think I can get further.
I guess if I wanted to change the format, I would leave it as text, and write a function to give the format I want, but in text.
Your( Hans) macro does returns the format as date, but not in the format Yasser wants, at least not in my Excel . Maybe it does in his Excel. I am not clear about that.
I think at the end of the day, the different date conventions are always a pain in the xxxx, and I would personally typically use the Long value representation in any calculations, and from that always display the date as text in the text format I want.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Fix dates when populating on worksheet
This is a crap solution, but this is the best i can do for now
This gets the things in the format wanted to be seen
Then I would do something like this to change the way it looks
In my work, I usually use something like this to display it in a good descriptive way
Format(Acel.Value, "dddd dd mmm yyyy")
I try to avoid an only number format like the plague. ( I have similar grief with the decimal separator, so I always use a full number ( no scientific format ) , I never use a thousand separator, and I let either a , or . to be taken as the decimal separator. Mostly also I always dimension a number variable as text: That helps a lot to avoid confusion, and VBA almost always accepts a text that looks like a number as the number it looks like )
This gets the things in the format wanted to be seen
Code: Select all
Sub PrettyBeautiful_Flip()
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
Code: Select all
Sub RightClickErsatz()
Dim Acel As Range
For Each Acel In Selection
Let Acel.Value = Format(Acel.Value, "dd mmm yyyy")
Next Acel
End Sub
Format(Acel.Value, "dddd dd mmm yyyy")
I try to avoid an only number format like the plague. ( I have similar grief with the decimal separator, so I always use a full number ( no scientific format ) , I never use a thousand separator, and I let either a , or . to be taken as the decimal separator. Mostly also I always dimension a number variable as text: That helps a lot to avoid confusion, and VBA almost always accepts a text that looks like a number as the number it looks like )
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Fix dates when populating on worksheet
The macro applies the system's short date format.Doc.AElstein wrote: ↑05 Nov 2021, 21:34Your( Hans) macro does returns the format as date, but not in the format Yasser want.
If Yasser wants another format, he has two options:
1) Change the number format in the macro, or
2) Apply the desired number format to column F manually, and remove the line that sets the number format from the macro.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 550
- Joined: 27 Jun 2021, 10:46
Re: Fix dates when populating on worksheet
>These sort of date format inconsitancies always crop up
They do if you start handling dates as strings. Keep the underlying data as an actual Date (which is a numeric value), and there is no problem. Except when Excel sometimes likes to turn a Date into a string without telling you, which is what Transpose is doing (or, more accurately, what is happening when you fill range from a Transposed Date array)
>The only way you can ever get a bullet proof result in Excel for consistent date formats
See above
>my Excel tells me that I have format TT-MM-JJJJ
Which tells me you have a German setup
They do if you start handling dates as strings. Keep the underlying data as an actual Date (which is a numeric value), and there is no problem. Except when Excel sometimes likes to turn a Date into a string without telling you, which is what Transpose is doing (or, more accurately, what is happening when you fill range from a Transposed Date array)
>The only way you can ever get a bullet proof result in Excel for consistent date formats
See above
>my Excel tells me that I have format TT-MM-JJJJ
Which tells me you have a German setup
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Re: Fix dates when populating on worksheet
Thanks a lot, everyone for sharing the issue.
According to what you say all of you, I took Alan's code and tried another idea which is the TextToColumns trick (I learned it from my tutor HansV)
First to format the range as Text then to use evaluate as Alan did and finally to use the TextToColumns trick
Can you please test that on your side so as to make sure the approach used is correct for all systems?
According to what you say all of you, I took Alan's code and tried another idea which is the TextToColumns trick (I learned it from my tutor HansV)
First to format the range as Text then to use evaluate as Alan did and finally to use the TextToColumns trick
Can you please test that on your side so as to make sure the approach used is correct for all systems?
Code: Select all
Sub Test_Final()
Dim dDate As Long, dDays As Long
With ActiveSheet
dDate = .Range("A1").Value2
dDays = Evaluate("=DAY(EOMONTH(" & dDate & ",0))")
With .Range("I1:I" & dDays)
.NumberFormat = "@"
.Value = Evaluate("=DAY(ROW(1:" & dDays & "))" & "&" & """/""" & "&" & "MONTH(" & dDate & ")" & "&""/""&" & "YEAR(" & dDate & ")")
.TextToColumns FieldInfo:=Array(1, 4)
End With
End With
End Sub
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Fix dates when populating on worksheet
Hi Yasser
Here some results of mine, after running your macro Sub Test_Final()
Three different computers, with different Excel versions
Excel 2007 – https://i.postimg.cc/XY0hQ4c2/Yasset-Da ... XL2007.jpg
Excel 2010 – https://i.postimg.cc/Y2ZTL14B/Yassers-D ... XL2010.jpg
Excel 2013 – https://i.postimg.cc/hPm6Lfxz/Yasser-Da ... XL2013.jpg
In all those 3 cases, the first 12 lines are formatted as Date, and from line 13 they are formatted as text
I will take a guess that if I fiddle with the internal date format settings in my registry, then I could probably get the macros to do what we want. But I don’t know if registries can be changed by VBA coding***, so that is probably not the answer either.. ( I personally stay with my opinion that problems always crop up with date inconsistencies between different systems, just IMHO, that’s all )
( *** Here is something from Amit Kabadi Posted July 22, 2009, https://www.toolbox.com/tech/programmin ... ly-072009/
, but I don’t know if I like the look of it – very complicated API stuff )
_.______________________________________________
( Ref: Dates in Registry: )
Here some results of mine, after running your macro Sub Test_Final()
Three different computers, with different Excel versions
Excel 2007 – https://i.postimg.cc/XY0hQ4c2/Yasset-Da ... XL2007.jpg
Excel 2010 – https://i.postimg.cc/Y2ZTL14B/Yassers-D ... XL2010.jpg
Excel 2013 – https://i.postimg.cc/hPm6Lfxz/Yasser-Da ... XL2013.jpg
In all those 3 cases, the first 12 lines are formatted as Date, and from line 13 they are formatted as text
I will take a guess that if I fiddle with the internal date format settings in my registry, then I could probably get the macros to do what we want. But I don’t know if registries can be changed by VBA coding***, so that is probably not the answer either.. ( I personally stay with my opinion that problems always crop up with date inconsistencies between different systems, just IMHO, that’s all )
( *** Here is something from Amit Kabadi Posted July 22, 2009, https://www.toolbox.com/tech/programmin ... ly-072009/
, but I don’t know if I like the look of it – very complicated API stuff )
_.______________________________________________
( Ref: Dates in Registry: )
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Fix dates when populating on worksheet
Hello
I did a few experiments… and thought a bit…
I see 2 main problem that I have been having:
_1 It seems that Excel decides what dates I actually have. In my earlier screenshots of mine, where the format looked OK, Excel has decided that the first date, 01/11/2021, was not the 1 November, as I had thought and wanted, but Excel saw that as 11 January. The second date, 02/11/2021 , was seen by Excel as 11 February, and so on. Hence in at some other times, I have seen some strange change in format when a number hits 13 – at this point Excel got confused and switched the day and month around
_2 It seems that the forward slash / has a special working. So using that anywhere can screw things up.
In my Excel it is likely that the forward slash / tells Excel that I am playing with dates, and often it will then revert back to the default date format ( which include a dash, - , for me ) and then it may or may not have the day and month in the order that I want.
So, bearing those 2 points in mind, and thinking again about what everyone else has said here, I have a solution that works for me
Two main parts to the solution, ( corresponding to the two code lines in the ' Beautiful solution version \ -_- / )
_ I put the dates in as their Long values
_ When I finally format the cells, I have to be careful how I use the forward slash / in any .NumberFormat code line. By trial and error I found that I need to do something like \/
I will take a guess that the backslash is working like some sort of thing to tell Excel that I want a literal forward slash / , so that Excel does not take a forward slash / as some instruction to do what it thinks it should rather than what I want
So these work for me, and afterwards I have custom format, but that is not text, (https://i.postimg.cc/90zkDB5K/Custom-Not-Text.jpg ) and so that right click formatting stuff works.
Whether it works for anyone else I don’t know….
Full workings like this:
In my Excels all is well…
https://i.postimg.cc/Kj5Nfk7F/Format-Slash.jpg https://i.postimg.cc/VvwgXBCD/Format-Slash-Works.jpg
Alan
I did a few experiments… and thought a bit…
I see 2 main problem that I have been having:
_1 It seems that Excel decides what dates I actually have. In my earlier screenshots of mine, where the format looked OK, Excel has decided that the first date, 01/11/2021, was not the 1 November, as I had thought and wanted, but Excel saw that as 11 January. The second date, 02/11/2021 , was seen by Excel as 11 February, and so on. Hence in at some other times, I have seen some strange change in format when a number hits 13 – at this point Excel got confused and switched the day and month around
_2 It seems that the forward slash / has a special working. So using that anywhere can screw things up.
In my Excel it is likely that the forward slash / tells Excel that I am playing with dates, and often it will then revert back to the default date format ( which include a dash, - , for me ) and then it may or may not have the day and month in the order that I want.
So, bearing those 2 points in mind, and thinking again about what everyone else has said here, I have a solution that works for me
Two main parts to the solution, ( corresponding to the two code lines in the ' Beautiful solution version \ -_- / )
_ I put the dates in as their Long values
_ When I finally format the cells, I have to be careful how I use the forward slash / in any .NumberFormat code line. By trial and error I found that I need to do something like \/
I will take a guess that the backslash is working like some sort of thing to tell Excel that I want a literal forward slash / , so that Excel does not take a forward slash / as some instruction to do what it thinks it should rather than what I want
So these work for me, and afterwards I have custom format, but that is not text, (https://i.postimg.cc/90zkDB5K/Custom-Not-Text.jpg ) and so that right click formatting stuff works.
Whether it works for anyone else I don’t know….
Code: Select all
Sub SlashIt() ' ' Beautiful solution version \ -_- / https://eileenslounge.com/viewtopic.php?p=289607#p289607
Let Range("F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "").Value = Evaluate("=Row(" & Evaluate("=DATEVALUE(""" & Range("A1").Value & """)") & ":" & Evaluate("=DATEVALUE(""" & Range("A1").Value & """)") + Evaluate("=DAY(EOMONTH($A$1,0))") - 1 & ")") ' I put the dates in as their Long values
Let Range("F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "").NumberFormat = "dd\/mm\/yyyy" ' When I finally format the cells, I have to be careful how I use the forward slash / in any .NumberFormat code line. By trial and error I found that I need to do something like \/
End Sub
Code: Select all
Sub Slash() ' https://eileenslounge.com/viewtopic.php?p=289604#p289604
Dim Ws As Worksheet: Set Ws = ActiveSheet
Dim LngStrt As Long, dDays As Long
Let LngStrt = Ws.Evaluate("=DATEVALUE(""" & Range("A1").Value & """)"): Debug.Print LngStrt ' 44501 - ( That ist 1st November 2021 )
Let dDays = Ws.Evaluate("=DAY(EOMONTH($A$1,0))"): Debug.Print dDays ' 30
Dim arrRwDts() As Variant
Let arrRwDts() = Ws.Evaluate("=Row(" & LngStrt & ":" & LngStrt + dDays - 1 & ")") ' - gets a "vertical" array of the long date numbers
Let Range("F1:F" & UBound(arrRwDts(), 1) & "").Value = arrRwDts()
Let Range("F1:F" & Evaluate("=DAY(EOMONTH(" & Range("A1").Value2 & ",0))") & "").NumberFormat = "dd\/mm\/yyyy"
End Sub
https://i.postimg.cc/Kj5Nfk7F/Format-Slash.jpg https://i.postimg.cc/VvwgXBCD/Format-Slash-Works.jpg
Alan
You do not have the required permissions to view the files attached to this post.
Last edited by Doc.AElstein on 06 Nov 2021, 18:28, edited 1 time in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Fix dates when populating on worksheet
To get Hans macro to work for me, I just need to change the last formatting line from "m/d/yyyy" to "dd\/mm\/yyyy"
The thing about the / answers my question of why my macro recording retuned me m/d/yyyy for when I manually selected short date format, - those / are meaning something specific to Excel telling Excel we are talking about dates, in this case the ' System short date format
Excel syntax is probably being a bit misleading here – I would have thought m/d/yyyy would be meaning me something like 1/2/2000 for the 2nd of January. It isn’t saying that. It’s saying ' System short date format which likely by me comes out at something like 02-01-2021 in my Excel and is the second of January, or first of February, depending on what’s in my registry for ' short date format ,
sShortDate here in the registrey:-
So that makes sense: It is along the lines of what people have been saying to me here, but as usual, it takes me a while to get the point. :)
Code: Select all
Sub Test()
Dim dDays() As Date, iYear As Long, iMonth As Long, DaysInMonth As Long, i As Long
let iYear = Year(Range("A1").Value2)
Let iMonth = Month(Range("A1").Value2)
Let DaysInMonth = Day(DateSerial(iYear, iMonth + 1, 0))
ReDim dDays(1 To DaysInMonth, 1 To 1)
For i = 1 To DaysInMonth
Let dDays(i, 1) = DateSerial(iYear, iMonth, i)
Next i
With Range("F1").Resize(DaysInMonth)
.Value = dDays
'.NumberFormat = "m/d/yyyy" ' System short date format
.NumberFormat = "dd\/mm\/yyyy"
End With
End Sub
Excel syntax is probably being a bit misleading here – I would have thought m/d/yyyy would be meaning me something like 1/2/2000 for the 2nd of January. It isn’t saying that. It’s saying ' System short date format which likely by me comes out at something like 02-01-2021 in my Excel and is the second of January, or first of February, depending on what’s in my registry for ' short date format ,
sShortDate here in the registrey:-
So that makes sense: It is along the lines of what people have been saying to me here, but as usual, it takes me a while to get the point. :)
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Fix dates when populating on worksheet
Hi
The date things discussed here have been niggling me a bit. It’s still my opinion that date formats are a bit tricky, especially if you are sharing around people with different date settings.
I have started some notes for my own amusement on the date stuff , and as this Thread set me off thinking a bit ( again) , I thought I would drop a last post here with a few quick comments, that's all.
As far as dates in Excel and excel VBA are concerned, I think a few things are either not documented or documented badly / sparsely, and if you want to share a file that will both
_ look finally to the end user in a specific format
and
_ any shown numbers relate specifically to any quantities ( day or month for example,)
, then you have to be very careful.
I think it should be possible to share a file and be pretty sure of how a given date is going to be received. I am not convinced yet that I can do that. Maybe
I have this file, , which I am going to add to as I try to sort the whole date business out in my mind. I have put a function, and some other stuff in it, to try to help give some clarity and constancy between what different people think a date is, in terms of the actual day its supposed to represent. If anyone out of curiosity wants to open that file , and give a screenshot of what you see, it might be interesting to compare what we think a date means. ( I put a bit of coding in the worksheet open, so you don’t have to do anything yourself to run any coding, just open the workbook and enable macros.)
( Also , If you have the time and interest you could return the file after it has opened in your Excel. But note that something’s will likely look different depending on in what Excel they are opened in and looked at )
Amongst other things, that file has a routine that tells you what your registry’s system short date format, sShortDate is. I have not dome that yet properly with API stuff , ( yet). The macro is a bit crude and probably may not always get it right. It’s based on what I have managed to research or understand from dates so far. It produces the “implied” system short date format, based on what some of the typical date related functions do. I have a feeling not many people really know how some of those function really work. The Microsoft documentation often does not give the full picture, so as often, we are left to guess.
I won’t clutter this thread with my detailed findings and conclusions: In any case I have not finished them yet… If you are interested you are very welcome to look at and comment over at that referenced post
One thing I think I can say:
If a text string or number that might in someway or another conceivably look like a date is put into a cell, manually or programmatically, Excel may do something to influence what you finally see. Exactly what Excel does will be depending on many factors including various settings. These settings may well be different in different Excel versions.
Possibly at some point of time someone somewhere may have been privy to the information to predict exactly what any Excel might do in any situation. In the meantime they have long since moved on and forgotten, so nobody knows for sure what’s going on anymore.
So playing around with dates , especially sharing files and macros between different people’s Excel is very tricky and full of problems. That’s my conclusion / opinion.
Alan
Share ‘DatesTests.xls’
https://app.box.com/s/tnhougmg746mfqywlewxseu3stm7txib
The date things discussed here have been niggling me a bit. It’s still my opinion that date formats are a bit tricky, especially if you are sharing around people with different date settings.
I have started some notes for my own amusement on the date stuff , and as this Thread set me off thinking a bit ( again) , I thought I would drop a last post here with a few quick comments, that's all.
As far as dates in Excel and excel VBA are concerned, I think a few things are either not documented or documented badly / sparsely, and if you want to share a file that will both
_ look finally to the end user in a specific format
and
_ any shown numbers relate specifically to any quantities ( day or month for example,)
, then you have to be very careful.
I think it should be possible to share a file and be pretty sure of how a given date is going to be received. I am not convinced yet that I can do that. Maybe
I have this file, , which I am going to add to as I try to sort the whole date business out in my mind. I have put a function, and some other stuff in it, to try to help give some clarity and constancy between what different people think a date is, in terms of the actual day its supposed to represent. If anyone out of curiosity wants to open that file , and give a screenshot of what you see, it might be interesting to compare what we think a date means. ( I put a bit of coding in the worksheet open, so you don’t have to do anything yourself to run any coding, just open the workbook and enable macros.)
( Also , If you have the time and interest you could return the file after it has opened in your Excel. But note that something’s will likely look different depending on in what Excel they are opened in and looked at )
Amongst other things, that file has a routine that tells you what your registry’s system short date format, sShortDate is. I have not dome that yet properly with API stuff , ( yet). The macro is a bit crude and probably may not always get it right. It’s based on what I have managed to research or understand from dates so far. It produces the “implied” system short date format, based on what some of the typical date related functions do. I have a feeling not many people really know how some of those function really work. The Microsoft documentation often does not give the full picture, so as often, we are left to guess.
I won’t clutter this thread with my detailed findings and conclusions: In any case I have not finished them yet… If you are interested you are very welcome to look at and comment over at that referenced post
One thing I think I can say:
If a text string or number that might in someway or another conceivably look like a date is put into a cell, manually or programmatically, Excel may do something to influence what you finally see. Exactly what Excel does will be depending on many factors including various settings. These settings may well be different in different Excel versions.
Possibly at some point of time someone somewhere may have been privy to the information to predict exactly what any Excel might do in any situation. In the meantime they have long since moved on and forgotten, so nobody knows for sure what’s going on anymore.
So playing around with dates , especially sharing files and macros between different people’s Excel is very tricky and full of problems. That’s my conclusion / opinion.
Alan
Share ‘DatesTests.xls’
https://app.box.com/s/tnhougmg746mfqywlewxseu3stm7txib
You do not have the required permissions to view the files attached to this post.
Last edited by Doc.AElstein on 24 Nov 2021, 21:11, edited 2 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Fix dates when populating on worksheet
Would you be so kind to remove words such as cr*p from your workbook?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Fix dates when populating on worksheet
Done it :)
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Fix dates when populating on worksheet
Thank you.
This is the result I get in Excel 2021 (32-bit):
This is the result I get in Excel 2021 (32-bit):
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans