Excel - Looking for method to execute Text to Columns

[john.hammond]

Having a bit of a mare with writing dates into an Excel column, which I'll briefly expand upon before mentioning what it is I'm after:

I'm sourcing data from a number of spreadsheets. This is collated by a Power Query (where the dates all appear in the format dd/mm/yyyy). These are read into a Blue Prism collection (where the dates all STILL appear in the format dd/mm/yyyy). Writing this collection to Excel randomly throws out the odd mm/dd/yyyy format (when the date is possible to do so, ie. 01/11/2023 will come out as 11th January, but 28/08/2023 will remain 28th August). Have searched high and low for a way around this (all locale settings appear to be correct), and the best I can currently do is pre-format the column in Excel to be Text and then write into it. This preserves the correct date format. Unfortunately, off the back of these date columns, I have a number of formulae that need to check the date(s) entered, which now do not function correctly. The only workaround I currently have for this is to perform the write in two steps - one up to the date column (the date column I write to is hidden, and another column picks it up in the correct format) and then a second write action for the rest of the collection in the proceeding columns. This is not ideal, however, and I'm looking for another solution.

What I would like to do is emulate the functionality within Excel that allows text to be parsed as dates (after the point of having written the data). Manually, I would do this by clicking Text-to-Columns, Delimited, Next, Next, Date (DMY). Would somebody be able to help me with the code stage required to do this, if it is even possible?

Many Thanks

[Yeswa_VaibhavNe]

Hi John,

As per my understanding of your query, you are trying to change the format of a column which has dates in it (automatically saving as MM/dd/YYYY) and you are trying to change the format to "dd/MM/YYYY". If my understanding is correct, this can be resolved with help of macro.

----Code---

Sub test()

    Dim wsThis As Worksheet

    Dim lastrow As Integer

    Set wsThis = ThisWorkbook.Worksheets("Sheet1")

    lastrow = wsThis.Cells(wsThis.Rows.Count, "A").End(xlUp).Row

    wsThis.Range("A1:A" & lastrow).NumberFormat = "dd/MM/yyyy"

End Sub

--------End of Code-----

I have tried it in macro and worked well. You could also try it.

correct me if I am wrong.

Regards,

Vaibhav.

[john.hammond]

Hi Vaibhav, thank you for your response.

It's funny that you mention performing a Macro, as I got that idea also as soon as I posted this. You're quite right, it does the job well, although I'd be interested to hear if there is a code stage-based solution, as I know that some recipients may not be permitted to open files with macros embedded. However, I think this is the best approach for the time being! Many Thanks.

[Yeswa_VaibhavNe]

Hi John,

Yes, that is a nice point as everyone wont be having permission to open macro. But its a script and we are invoking it from our end, So, I think there wont be any issue with others opening the macro as they can't see this. We can use this macro as supporting file.

Thank you

[MichealCharron]

@john.hammond & @Yeswa_VaibhavNe

If you are just looking to set the date format of a column, you can use the "Format Cell" action in the "MS Excel VBO" VBO. "Format Cell" is somewhat of a misnomer for that action in that you can also format ranges. Take for example the following stage:

It sets the date format of the entire "F" column to "dd/MM/yyyy". Also, because it sets the format at the column level, as opposed to the individual cell level, it results in smaller file sizes and new rows added are automatically formatted accordingly.

[Yeswa_VaibhavNe]

Hi @MichealCharron 

Yes your option is actually good as it is inside the Blue Prism. Thanks for the answer.