Fix dates when populating on worksheet

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Fix dates when populating on worksheet

Post by YasserKhalil »

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.

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Fix dates when populating on worksheet

Post by Doc.AElstein »

YasserKhalil wrote:
05 Nov 2021, 20:02
all the output es treated as text no as a date. I just need one macro that treat the output as dates.
:scratch:
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
https://i.postimg.cc/nzpZ2cp4/Date.jpg
Date.JPG






_.____________________________________________________________________________________________________

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
https://i.postimg.cc/43zsphk4/Custom.jpg
Custom.JPG
https://i.postimg.cc/GtTLfgTr/TT-MM-JJJJ.jpg
TT-MM-JJJJ.JPG


_.________________________________________________________________________________


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

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Fix dates when populating on worksheet

Post by YasserKhalil »

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.

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Fix dates when populating on worksheet

Post by Doc.AElstein »

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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Fix dates when populating on worksheet

Post by HansV »

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

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Fix dates when populating on worksheet

Post by YasserKhalil »

I tested the output in column F after executing that code

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
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
Untitled.png
When applying any format like that, it doesn't work
Untitled.png
You do not have the required permissions to view the files attached to this post.

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Fix dates when populating on worksheet

Post by Doc.AElstein »

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
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Fix dates when populating on worksheet

Post by HansV »

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.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Fix dates when populating on worksheet

Post by Doc.AElstein »

HansV wrote:
05 Nov 2021, 21:21
Changing the number format to a date format afterwards doesn't change the values
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

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Fix dates when populating on worksheet

Post by Doc.AElstein »

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

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
Then I would do something like this to change the way it looks

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
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 )
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Fix dates when populating on worksheet

Post by HansV »

Doc.AElstein wrote:
05 Nov 2021, 21:34
Your( Hans) macro does returns the format as date, but not in the format Yasser want.
The macro applies the system's short date format.
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

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: Fix dates when populating on worksheet

Post by SpeakEasy »

>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

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Fix dates when populating on worksheet

Post by YasserKhalil »

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?

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

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Fix dates when populating on worksheet

Post by Doc.AElstein »

Hi Yasser
Here some results of mine, after running your macro Sub Test_Final()
Three different computers, with different Excel versions

Excel 2007https://i.postimg.cc/XY0hQ4c2/Yasset-Da ... XL2007.jpg
YassetDatesKB_XL2007.JPG

Excel 2010 https://i.postimg.cc/Y2ZTL14B/Yassers-D ... XL2010.jpg
YassersDatesMartin_XL2010.JPG

Excel 2013 https://i.postimg.cc/hPm6Lfxz/Yasser-Da ... XL2013.jpg
YasserDatesPolak_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

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Fix dates when populating on worksheet

Post by Doc.AElstein »

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….

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
Full workings like this:

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
In my Excels all is well…
https://i.postimg.cc/Kj5Nfk7F/Format-Slash.jpg
FormatSlash.JPG
https://i.postimg.cc/VvwgXBCD/Format-Slash-Works.jpg
Format Slash Works.JPG
ImageImageImage

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

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Fix dates when populating on worksheet

Post by Doc.AElstein »

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"

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

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:-
Image

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

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Fix dates when populating on worksheet

Post by Doc.AElstein »

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,
DatesTests.xls
, 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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Fix dates when populating on worksheet

Post by HansV »

Would you be so kind to remove words such as cr*p from your workbook?
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Fix dates when populating on worksheet

Post by Doc.AElstein »

Done it :)
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Fix dates when populating on worksheet

Post by HansV »

Thank you.
This is the result I get in Excel 2021 (32-bit):

S0929.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans