Date Format Issue once again

Hello experts.
I am having an issue with the dates. The source sheet B2B has dates starting from cell E7 to last row. When I run the code, the data extracted is showing the incorrect dates. The actual data in the source sheet are dd/mm/yyyy and the extracted dates are mm/d/yyyy of the dates from 1st of each month to the 12th of that month only. I decided to add some code lines in the beginning of the code to replace the dates in the source sheet from dd/mm/yyyy to dd-mm-yyyy, but I am still facing the same issue. Hope someone can chime in and get me the correct dates with the right code.
For some reason I am not able to upload the file. Upload of the file failed message....

Cut down the file - post JUST a sample showing the issue (you should know this by now).

Yes AliGW. I forgot that there were multiple hidden sheets in the file. Now I have deleted them and uploading the file.

File/sheet is password protected!

Originally Posted by RAJESH SHAH

When I run the code, the data extracted is showing the incorrect dates.

Hello,

as yet seen in your previous threads the classic case is when so called 'dates' are text instead of Excel numbers as dates,
the classic case with data badly imported so the easy way is to just correct the data importation …
Another case may be a bad code … In Data menu you can use Text To Columns Excel feature to convert text to date.

Edit : as no Excel date in your attachment - bad import again ! - so just convert the text to date …

Password is 123

Originally Posted by Marc L

use Text To Columns Excel feature to convert text to date.

The text to column approach worked. Thanks Marc L.

(removed as was initially posted within another thread …)

Marc L you keep telling to activate the Macro Recorder and I don't know what are you suggesting. Can you explain me in simple terms please ? I checked all the modules and I was able to zero the issue to the GetMasterData module but I could not edit the code and correct it.

Before hitting the sack, this one post which is still not resolved I could like to explain the issue. The dates are correctly formatted in the B2B sheet if I call the module ConvertTextDatesFormat which is still not connected to the other modules. The extracted results in 2A, 2A copy, 2A Extract and ImportPurchases sheet showing the incorrect format of the same date as in B2B. In the Source sheet B2B , the actual date values are dd-mm-yyyy. If the date format in 2A copy and 2A sheets are corrected then automatically the ImportPurchases too will be corrected. Comment the Hidesheets line in the clearData module to avoid unhiding the sheets again and again.
Please note: I have pasted a copy of the dates of column E and P in unwanted columns D and O to test and compare the correct dates.
Happy coding and Good Night.

Originally Posted by RAJESH SHAH

The text to column approach worked. Thanks Marc L.

The method will only work properly if the date in text form has the same order of days and months as your system date.
If you have text: 03/07/2023 and your system date has the format dd-mm-yyyy (there may be other date separators), then using Text To Column will return the correct date. In case your system format is mm-dd-yyyy, then Text To Column will return an invalid date. Instead of 07-03-2023 (the third of July), you will get 03-07-2023 (the seventh of March).
If you have a date in text form in the cells, use the text functions to extract the components of the date, and then use the DateSerial function to create the date. The following modification will return the correct dates regardless of the order of days and months in the system date format, assuming that the source texts have the sequence: dd-mm-yyyy.

Please Login or Register  to view this content.

Note that you are not formatting the dates, only the cells.

However, this macro should not be used.
You should focus on importing the data correctly so that the dates and numbers are actually dates and numbers.

Artik

Originally Posted by Artik

As a test, I switched my system date format to American (m/d/yyyy).

I just did the same test with bad text column B2B worksheet from post #3
and whatever the system date, European like US Text To Columns still well works
like since last century with different Excel versions and hundreds text to date conversions …

Edit : like I wrote « Text To Columns always works if just well used » and
I reproduced your result just misusing it so your bad as you did not select the correct original date format
within the last Assistant window, the classic case, 99% of the time when people said Text To Columns does not work as expected …

Once with a correct setup, bad original Text in column D
and Text To Column Date conversion in column F :

Text2Date.jpg

Just to clarify what Marc is saying, if you are converting American to British, you choose MDY in the final T2C window (because MDY is the order of the original date - momth day year); if you are going from British to American, you need to choose DMY.

I have been caught out by this myself in the past, thinking that I needed to choose the date format that I wanted the dates to change to.

Hello Artik, I tested your code and it works for the B2B sheet. The text to columns too works fine. But either way when I extract the text from B2B to sheet 2A Copy the date format is changed from 03/01/2022 to 01/03/2022. In the 2A copy module how do I edit the code to get the date values correctly.? Even the extracts from 2ACopy to 2A and 2A to 2AExtract sheet are showing the incorrect dates. I would appreciate it if you can please check and help me correct that too. Using F8, I was able to find that the issue is in these 3 connected modules which are converting the dates in the incorrect format.. ( Create 2A, Create Not Imported and Print 2A Edited ).

FYI, The source sheet is downloaded and the date is always displayed in the same format (dd/mm/yyyy). My system (in India) is always dd-mm-yyyy.

Please provide a small sample of the data exactly as it is downloaded for me to look at.

Just because 'dates' look like dates and have date formatting applied, doesn't mean they ARE dates, as others have mentioned before.

Sure. This is a sample of the B2B as downloaded.

The dates are in the general format but read as dd/mm/yyyy

OK - so can you see that the dates are left-aligned (not right-aligned)? That's the first clue.

Socondly, if you select any of the dates and look at formatting on the Home ribbon, you will see that the formatting is GENERAL, but what you are seeing look like dates. This is the giveaway: if they were REAL dates, they would show as 5-digit serial numbers.

So, your 'dates' are not dates.

I will update one of the columns for you in a moment, then reshare the workbook so that you can make comparisons so that you finally understand the issue.

The dates are in the general format but read as dd/mm/yyyy

Yes, that's because they are text, not real dates.

OK - so in the attached, look at column E: you will see that the entries now show as 5-digit serial numbers. This is because I used text to columns (with DMY at the end) to convert them to dates (but I had to remove the merged formatting first).

You will also notice that the formatting of this column (E) has been set to NUMBER - I did this so that you can see the difference: compare this to column P, where the 'dates' have not yet been converted, and you will see that the ones in P do NOT show as serial numbers as they are in fact still text.

Now select column E and change the formatting on the Home ribbon to SHORT DATE: you will see the serial numbers formatted as dates. Column E now contains real dates, whilst column P still contains text.

Take your time over post #29: you will NEVER get to grips with the issues you are having until you understand this.

I have tried to explain it as clearly as I can so that you understand: there is no point in your moving on until you do understand it.

Then you can practise converting column P to dates: select the column (remove any merged formatting that is applied) > Data Ribbon > Text to Columns > Next > Next > select DMY > Finish. The dates will now be serial numbers (because they are real dates): apply SHORT DATE formatting to see them as dates.

The issue in B2B sheet is resolved either way by the code shared by Artik and also by text to columns as suggested by Marc L. In you solution even if I manually edit the B2B sheet and convert the numbers to short date and then run the code, the problem in the connecting sheets remains the same (Date format changed once again).The issue now is the result in the connecting sheets where the date format is copied and pasted in an incorrect format once again.

OK - I just took quite some time to produce a sample workbook for you and talk you through something that you have not displayed a full understanding of up until now.

I would have thought a 'thank you' might have been appropriate, even if you think it a useless waste of my time and yours.

It would appear to me, as a VBA novice, that something in the code is conflicting with the changes you have made, or the code simply isn't choosing the right format for the source data, which is DMY. I do not understand code well enough to debug it, so over to you and Artik.

Thanks AliWG for explaining and making it so simple to understand. This is the updated workbook with one of the issues solved. The connected sheets need to be solved. Hopefully someone will be able to solve this problem.

None of the four date columns in the B2B worksheet is showing dates - they are ALL text. Formatted as GENERAL, if they were dates, they would be showing as 5-digit serial numbers.

In the Source sheet B2B , the actual date values are dd-mm-yyyy

No, they are text that look like dates in the format dd/mm/yyyy.

If you run the code once again, the text in the B2B sheet will be converted to short date as I have connected Artik's code to the clear data module in the end of the module. Likewise, I think I will need to do something like that in all the other modules.

Artik's code is for column E - I don't see anything for column P.

Why is the code set to change the formatting to dd-mm-yyy? Because by doing that, you are probably going to end up with text.

As I said, my coding skills are very basic indeed, but my best guess is that something here needs adjusting:

Please Login or Register  to view this content.

If you check the B2B sheet after running the code, if you place the cursor in the date cell it will display as date.

But then again, as the others have been saying, why not just deal with the issue in the source file BEFORE importing it?

Using the original B2B source data file you provided earlier, I recorded this macro to convert columns E and P to dates:

Please Login or Register  to view this content.

It may not be pretty, but it works. What's more, when I then copy and paste those dates to another worksheet, they copy across as proper dates, not text.

I think you should be making the conversion at source.

I already did that. As you suggested to select date column, text to columns, dmy, short date, etc., then I ran the code. The result was incorrect dates once again. The present problem must be in the connected modules which needs to be edited accordingly.

Then just don't use the code for changing text to dates! If you have already converted them, you don't need it! Cut it out of the code.

You don't seem to be working through this methodically at all trying to eliminate issues one-by-one.

What I meant was I commented the code line Call ConvertTextDatesFormat.ConvertTextDatesFormat, followed the steps you shared to convert the values to dates manually and ran the code. I am facing the same issue in the connected sheets no change.

Then you need to work methodically. Use just ONE of the generated sheets and step through the code bit by bit commenting sections out until you find one that is the culprit.

You are ending up with a complete hotch potch of real dates, dates that are text and those with - as the delimiter that are text.

You need to do the testing: once you have gone through and tested everything in the code, you should know what's causing it. You can then tell the panel the lines of code that are cuasing the issue and take it from there.

I have no real coding experience at all, so this is how I would do it.

JUmping in late!

Using file from Post#41:

With Sheet "B2B" : run date conversion and dates in column E are converted to dates with format dd-mm-yyyy: formatting as GENERAL they are a number.

Hence as Ali has pointed out, run the conversion on ALL date columns in B2B and any extracted files should then be correctly converted to Excel dates,

e.g Dates in column E of 2A, A are dates BUT formatted as dd/mm/yyyy (UK standard format)

OK - using the workbook attached to post #3, I have done the following on the B2B sheet:

1. Manually converted text to dates using Text to Columns in both E and P.
2. Double-checked that the text has converted correctly to dates - it has.
3. Run the macro, which works ONLY on column E.

Observation: any date whose day and month are smalller than or equal to 12 do NOT take the formatting. The ones that do are, in the process, converted to text.

In the attached I have:

1. Manually converted text to dates using Text to Columns in both E and P.
2. Double-checked that the text has converted correctly to dates - it has.
3. Recorded a macro to convert the date FORMAT. When recording, I selected columns E and P together, chose CUSTOM FORMAT on the home ribbon and set date to dd-mm-yyyy.

Observation: you end up with ALL dates in the format you want AND they are real dates, not text.

This is the sort of methodical testing that you need to learn to do yourself.

So, if it were me, I'd simply record a macro to do the text to dates conversion using Text to Columns and apply the date formatting all in one go, and use that.

So once the columns are converted to numbers (aka dates) you can't have any more issue if the Excel logic is well understood & followed.

But there are still issues with the code as it stands, Marc (Artik's code) - I have proved that to myself by testing it and explained so in my previous post. I have also proved that what he wants is possible via the record macro route, so there is a way forward in all of this (I hope).

Marc - are you not able to follow the steps I took in post #43? What, if anything, do you think I was doing wrong in proving the issue?

Rajesh is not going to be able to produce what you want - we know that, and that's why I decided to do some testing myself, BUT I am not a VBA expert.

For information: I ran just Artik's code to convert text to dates (which it doesn't actually do).

Please check my findings. Thank you.

Sorry, I meant post #42. No posts have been deleted to my knowledge.

I did not think that you were contesting my findings, Marc, but I am not confident with VBA issues, so hoped that you wouldn't mind checking my findings.

once Text converted to Dates so once stored as numbers ... there is no more issue …

My findings, as described in post #42, are that this is not the case. I converted the text to dates (numbers) and checked that Excel was seeing them as numbers - it was. I then ran Artik's code, which works on column E, and the dates in column E were treated in the way I describe in post #42, leaving Rajesh with a mixture of real dates and text, so the code is in fact (a) not affecting all of the dates in the column and (b) those that are affected by the code are turned back into text.

What I am asking you is this: did I miss a crucial step between converting the dates to text and running the code? I wish to know for my own edification as much as anything else. In other words, are you able to replicate what I did in post #42? What am I missing or not understanding?

So IMO : Artik post #14 code is useless once Text is already converted to Date (numbers).

I will happily agree with that: what it does do is turn numbers into text or does nothing at all.

So we are agreed on one thing: that Rajesh should deal with the text to date issue BEFORE importing the data into his main workbook. I think this is by far the best advice.

By the way, as you have mentioned it, your English is not rough, but sometimes you are quite condescending to and make personal comments about people who are looking for help. That's why you sometimes receive warnings.

Yes Truth may hurt …

Originally Posted by Marc L

If a Text column must be converted as a Date column
then if really a code is necessary just with a single statement
via VBA TextToColumns method which never failed on my side since last century …

The following VBA procedure has 'no sense' again if the importation could - must ! - be directly corrected …

Anyway according to post #3 attachment in order to convert worksheet B2B columns E & P from dd/mm/yyyy to DATE dd-mm-yyyy
(conversion solved yesterday by a young Excel beginner just using Text To Columns feature and Macro Recorder)
without any issue if these columns are already DATE rather than TEXT :

Please Login or Register  to view this content.

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

The procedure you have just provided is precisely what I was expecting Artik's code would do.

I don't understand the need for dates to be displayed in that format, though, and it's an added and in my opinion unnecessary step (it's just aesthetics). Much better to focus on the data itself than how it looks - there's nothing inherently wrong with the default dd/mm/yyyy formatting, anyway.

Thank you for adding this routine, though, as it may be the answer to satisfy Rajesh.

Yes Truth may hurt …

Sure, as long as it's delivered kindly and objectively. If it isn't, it becomes a personal attack, and these are not tolerated here, as well you know.

Marc L. Your code is the same as Artik's. It is correcting the format to dd-mm-yyyy correctly in the B2B sheet only. The multiple sheets which are extracted with a code from B2B are all showing incorrect dates. Please check only the 2nd row result of B2B in the extracted sheets. They have changed from 03/01/2022 to 01-03-2022. Is it possible to get the values of column E as it is to the extract sheets. The ultimate final sheet which is Importpurchases will get the text format yyyymmdd correctly from the value I hope.

The answer lies in Mr.EXcel forum where the same issue was solved by JohnnyL more than 2 years back. As I was banned from the forum for posting in other forum's without intimation, I am not able to log in or view the result.

Rajesh - this is the WRONG way to be going about all of this. Why the need for the dd-mm-yyyy formatting? It's just an aesthetic - dd/mm/yyyy works just as well, so why do you need this unnecessary step at all?

You really need to reassess this sensibly.

I have tried my best in all possible ways but to no avail. Hope someone will wave their magic wand and ...Viola 🤞

The answer lies in Mr.EXcel forum where the same issue was solved by JohnnyL more than 2 years back

You must have a workbook that contains the code provided to you at that time, then. I suggest you find it.

What was your user name on Mr Excel?

I think you mean 'voilà' - a viola is a flower or a musical instrument.

Yup. I will do that tonight. I will search for the file and I hope I will be able to understand the code to edit it. Good LucK to me.

Answer ppst #59, please.

I don't remember the user name or password. It has been a long time. Maybe Rajesh Shah....

Was it this thread?

https://www.mrexcel.com/board/thread...-yyyy.1208658/

You can view it without having to log in, and I found it without logging in, as I ,too, was banned from MrE for far less than your sin.

voila also means Bingo

It does when it's spelt properly!

Was the thread the right one? It came top of the list when I Googled:

mrexcel vba date format JohnnyL

Yes. It is Rajesh1960. Now I remember after watching the image. The 2A data projected was abandoned and discontinued as it was not helpful. I don't keep the unwanted files or save them but I will check if I have the file saved somewhere.

The code is in that thread - can't you just copy it?

Please Login or Register  to view this content.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Weird. I tried to copy all the solutions from the bookmark but I was not able to open it as I am using the same system since 9 years. Maybe you are using a different system now so you were able to view it.

Follow the link in post #63.

Then mark this thread as SOLVED and don't forget to thank those who helped you. Some rep would be nice for those of us who have bent over backwards to try and help you. I didn't think I was going to have to do a Google search for you as well ...

Please Login or Register  to view this content.

I think this is it. But I have to wait for someone to transform my code accordingly. I am not so good at editing such complicated codes.

I think you are pushing at a closed door now. The boat has sailed.

See post #70.

Originally Posted by Marc L

No my code is very different ‼
As it shows how to convert a bad Text column to a good Date column like my post #57 as well if you just well read them …

So if B2B columns are DATE and not TEXT then delete your bad code in order to restart without modifying cells values
as on my side when a cell is a Date it is still the same date when I copy it to another worksheet, Excel / VBA basics …
For more help just follow my post #45 as I won't guess anything…

No doubt it is different. But what I am saying is that it is giving me the same incorrect results as before.

Originally Posted by AliGW

Follow the link in post #63.

Then mark this thread as SOLVED and don't forget to thank those who helped you. Some rep would be nice for those of us who have bent over backwards to try and help you. I didn't think I was going to have to do a Google search for you as well ...

I can't open the link to the posts as it is displaying that you are banned.

How can I close this unsolved post.? I am grateful to everyone who is hell bent to solve this. Thank you very much. I assume John Topley or Johnnyl or could be anybody, may still spring a surprise to help me solve this. You never know... So, till than I don't any other option but to wait for the solution.

I can't open the link to the posts as it is displaying that you are banned.

That YOU are banned! You aren't logging in as me. You will need to delete cookies for Mr Excel in your browser, then you can view anything. Mr Excel still thinks you're logged in.

Anyway here are two key messages from that thread:

Rajesh1960: I have been trying to edit and correct it since morning. Which line did you exactly edit or add....

JohnnyL:

Please Login or Register  to view this content.

It was

Please Login or Register  to view this content.

Originally Posted by Marc L

RAJESH,

you still misread !
By the way your post #71 code is the slow useless way to convert Text cell by cell to Date
instead of converting column at once like my posts #51 & 57.

So weird you ask for help but you do not ever try to well read and test the help you receive …

I read both the posts and tested both the codes individually. I have been telling you that the dates are displayed correctly as expected in the B2B sheet. The problem is the result sheet where the dates have changed. from 03/01/2022 to 01/03/2022

I assume John Topley or Johnnyl or could be anybody

They are both somebody, but JohnnyL is a member here, too. He'd have responded to this thread if he wanted to help you.

Yes AliGW. But where to place these lines in the code is not my cup of tea. Hope it helps other members who are viewing this. Thanks for sharing. Time for dinner. Will be back in an hour.

Good luck with that, Rajesh. There have been over 500 views of this thread so far and only a handful of members have offered help. Others will have seen how frustrating it has been for all of us, so I guess they've decided not to get involved.

My take on it is that you are going about the whole thing in the wrong way and that you don't need to reformat the date columns from dd/mm/yyyy. This is meant to be a functional spreadsheet, not a peice of fine art.

You cannot close this thread if you still want help with it.

Thoroughly confused here:

in B2B Row 8, columns D & E have date 03/01/2022

In 2Acopy and 2A column D has 01/03/2022 and E has 03/01/2022: As I understood column D was only a reference for TEXT vs Date, so the column E in the "2A" sheets appears correct.

There are many mis-matches betwen D & E in the "2A" sheets.

No more to add as Ali / Marc have exhausted all possibilities!!

@RAJESH: at the risk of repeating some things that you may have been told already, some thoughts that may help the diagnostic process.

1. Cells can be manually aligned left, centre or right. Also, but not important, top, centre and bottom. You can see what, if any, alignment has been set by checking the icons on the ribbon. So, that's the first thing to look at.
2. If the cells have no manual alignment, then text strings, including text strings that look like numbers/dates, will be left aligned. Numeric values, including dates and times will be right aligned.
3. If a cell has text in it, it does not matter what format is applied to the cell, it will not change how the data is displayed. I know this has been said already: formatting is applied to the cell, not the data. Numeric formats will only have an effect if the data is numeric.
4. It will not always be obvious if the data is DMY or MYD format. You need to know where the data has come from and what regional settings were used. As there are no months 13 to 31, that is the usual visual clue and you should then expect all other "dates" in the column to be consistent. So, if you see 07/31/2023 that, and all the other dates in the column, will be in DMY format. If they're not consistent, then you have a big problem.
5. When you use Text to Columns to convert text dates to numeric dates, you need to select the format of the source data, not the format you want to end up with. So, again, if some of the dates look like 07/31/2023, then pcik MDY format, not DMY.
6. Once the data has been converted using Text to Columns, do NOT run the macro again. That will convert dates like 01/03/2023 to 03/01/2023.

The code below operates on column E, starting in row 7. There is a simple recorded macro and an edited version of that same macro. The edited version allows you to set the sheet name, the column to be converted, and the start row at the beginning of the code. It is fairly straightforward to make this a called macro and pass it parameters. It also sets the format to the Custom Date format: "dd-mm-yyyy".

Also note that there is a check to see if the whole range to be converted is already numeric. If it is, it does not execute the Text to Columns code. You can remove the MsgBox but don't remove the Exit Sub.

Recorded Macro

Please Login or Register  to view this content.

Edited Macro

Please Login or Register  to view this content.

@Trevor:

Please Login or Register  to view this content.

If the above format is required

@John: good thought Thanks.

Originally Posted by JohnTopley

Thoroughly confused here:

in B2B Row 8, columns D & E have date 03/01/2022

In 2Acopy and 2A column D has 01/03/2022 and E has 03/01/2022: As I understood column D was only a reference for TEXT vs Date, so the column E in the "2A" sheets appears correct.
There are many mis-matches betwen D & E in the "2A" sheets.

No more to add as Ali / Marc have exhausted all possibilities!!

John Topley. The columns D and O data is not needed in the extract. So, I overwrote the data with dates. I have made a copy of column E and P in columns D and O just to match the result after the code is run. The source sheet is B2B and the Not Imported is blank when the B2B sheet is moved in the workbook. Rest of all the sheets are the code extracts the data from sheet to sheet and finally the final result is the Import Masters and Import Purchases sheets from where the xml file is created and saved on the desktop.

@RAJESH: please see the macro Trevor has provided which is an excellent generic macro to be used to validate dates in any worksheet.

Re post #88: I KNOW they are not needed: hence my comment that columns E in B2B and 2A and 2ACopy align: it was YOU who said the dates did not align.

Trevor. The result sheets 2A copy, 2A, Not Imported and 2A Extract are blank when I run the code except for the second row where formulas are applied in some cells. I tested and ran your code separately and got the dates corrected to dd-mm-yyyy in the B2B sheet.
The main issue is when I run the extract code the code extracts the data from B2B sheet to the various sheets, the date format of dates 1 to 12 are shown as months. Please run the code once so that you will know that the issue is in the other modules code lines where date is printed in various sheets. After running the module you shared, the dates are corrected in the B2B sheets only. If you need I can share the workbook once again.

Please Login or Register  to view this content.

This is the code which is copying the data from B2B to 2A Copy. I can't understand when the data is copied why are the dates printed incorrect. Can it be edited to get the same values as in column E...???

I know this is a huge project. I have just compared codes, understood and edited them and created this. It took me a year to complete it. But you people, being professionals you can run the code and find out which line is changing the dates in the wrong direction. Using F8 I was able to find that the Create_2A is one of the modules where the date is going in the wrong direction.

Trevor.
Your point 4. The downloaded data is read as DMY. As this is downloaded in january you can see that the date format is dd/mm/yyyy. But when I run the code it generates 2 xml files which I upload it in the system. Everything is perfect except for the dates. As the dates are copied wrongly to the connecting folders the whole project is a waste if I am not able to correct the issue.
Your point 6. This is the reason I have kept 2 columns of dates. When I have to test the code once again I copy the dates from D to E and from O to P and then test it again.

The discussion, as per the thread title is "Date Format Issue". And the past few posts seem to have revolved around columns E and P in the sheet B2B. Those columns have text values in them that, unfortuitously, look like dates. And the solutions and dialogue have looked at converting those values, reliably, into true numeric dates.

And that's where I, as an interested bystander, was tempted to join in, in the vain hope of drawing this thread to a close.

I am not planning on being sidetracked into an everlasting investigation into a project possibly/probably largely based on the output from ChatGPT.

It's your project. And it's your code. It's down to you to step through the code monitoring the input and output until you identify the point where it all goes horribly wrong. I knew it was a mistake to join in here, but I couldn't resist. My error.

What can I say ? Thanks for your time Trevor.

This query is shared in another forum for a working solution.
https://forum.ozgrid.com/forum/index...71-date-issue/

As stated earlier, you have exhausted both the patience and goodwill of many contributors so it is over to you and if you are lucky, OZGRID.

My last word on this subject. You were told way back to convert the dates at source. That source for the Create_2A macro is sheet B2B.

You clearly did not listen to, or understand, that advice. The test macro below demonstrates what is happening when you copy a range in sheet B2B to an array (ar1), process that array (ar1) into a second array (ar2), and output the second array (ar2) to sheet 2A Copy.

The solution, as I think you have been advised at several points, is to convert columns E and P in sheet B2B to true dates using Text to Columns (DMY).

The issue is that VBA is US oriented so, when it outputs the array it tries to interpret the data based on US Regional Settings, regardless of where you are.

Please Login or Register  to view this content.

PHP Code: 

01/01/2022    01/01/2022    01-01-2022    01-01-2022    44562    44562
01/03/2022    03/01/2022    01-03-2022    03-01-2022    44621    44564
31/01/2022    31/01/2022    31/01/2022    31-01-2022    31/01/2022    44592
28/01/2022    28/01/2022    28/01/2022    28-01-2022    28/01/2022    44589
29/01/2022    29/01/2022    29/01/2022    29-01-2022    29/01/2022    44590
31/01/2022    31/01/2022    31/01/2022    31-01-2022    31/01/2022    44592
01/01/2022    01/01/2022    01-01-2022    01-01-2022    44562    44562
31/01/2022    31/01/2022    31/01/2022    31-01-2022    31/01/2022    44592
19/01/2022    19/01/2022    19/01/2022    19-01-2022    19/01/2022    44580
30/07/2021    30/07/2021    30/07/2021    30-07-2021    30/07/2021    44407
01/03/2022    03/01/2022    01-03-2022    03-01-2022    44621    44564
28/01/2022    28/01/2022    28/01/2022    28-01-2022    28/01/2022    44589
01/01/2022    01/01/2022    01-01-2022    01-01-2022    44562    44562
01/01/2022    01/01/2022    01-01-2022    01-01-2022    44562    44562
01/08/2022    08/01/2022    01-08-2022    08-01-2022    44774    44569
01/10/2022    10/01/2022    01-10-2022    10-01-2022    44835    44571
01/04/2022    04/01/2022    01-04-2022    04-01-2022    44652    44565
01/05/2022    05/01/2022    01-05-2022    05-01-2022    44682    44566
01/05/2022    05/01/2022    01-05-2022    05-01-2022    44682    44566
01/01/2022    01/01/2022    01-01-2022    01-01-2022    44562    44562
01/10/2022    10/01/2022    01-10-2022    10-01-2022    44835    44571
01/03/2022    03/01/2022    01-03-2022    03-01-2022    44621    44564
22/01/2022    22/01/2022    22/01/2022    22-01-2022    22/01/2022    44583
01/05/2022    05/01/2022    01-05-2022    05-01-2022    44682    44566
01/07/2022    07/01/2022    01-07-2022    07-01-2022    44743    44568
01/11/2022    11/01/2022    01-11-2022    11-01-2022    44866    44572
13/01/2022    13/01/2022    13/01/2022    13-01-2022    13/01/2022    44574
31/01/2022    31/01/2022    31/01/2022    31-01-2022    31/01/2022    44592
31/01/2022    31/01/2022    31/01/2022    31-01-2022    31/01/2022    44592
01/01/2022    01/01/2022    01-01-2022    01-01-2022    44562    44562
01/03/2022    03/01/2022    01-03-2022    03-01-2022    44621    44564
01/05/2022    05/01/2022    01-05-2022    05-01-2022    44682    44566
01/05/2022    05/01/2022    01-05-2022    05-01-2022    44682    44566
01/01/2022    01/01/2022    01-01-2022    01-01-2022    44562    44562
01/01/2022    01/01/2022    01-01-2022    01-01-2022    44562    44562
01/03/2022    03/01/2022    01-03-2022    03-01-2022    44621    44564
01/12/2022    12/01/2022    01-12-2022    12-01-2022    44896    44573
13/01/2022    13/01/2022    13/01/2022    13-01-2022    13/01/2022    44574
19/01/2022    19/01/2022    19/01/2022    19-01-2022    19/01/2022    44580
01/09/2022    09/01/2022    01-09-2022    09-01-2022    44805    44570
01/05/2022    05/01/2022    01-05-2022    05-01-2022    44682    44566
12/09/2021    09/12/2021    12-09-2021    09-12-2021    44451    44539
01/05/2022    05/01/2022    01-05-2022    05-01-2022    44682    44566
30/01/2022    30/01/2022    30/01/2022    30-01-2022    30/01/2022    44591 


What can I say ? Thanks for your time Trevor.

You're welcome.


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Crawling back out from under my rock ...

The following code is what I came up with that can be used to check 'dates' in a column to see if they are actually dates, if the cell looks like a date, but is actually not date, the code will convert the cell to an actual date & you can also set what the actual date should look like in the cell.

The Sub code that does the conversion:

Please Login or Register  to view this content.

If you copy that code into a regular module in the workbook that you want to fix the 'dates' in ... example 'B2B as downloaded' You can then add another module to call the code:

Please Login or Register  to view this content.

To test if a cell is an actual date, you can use the following code, as an example:

Please Login or Register  to view this content.

I will recede back to the shadows now.

Thank you everyone who contributed their time trying to help me to solve the issue. The issue has been resolved now on Ozgrid. I didn't have to use the ConvertTextDatesFormat module at all.