Hi folks,
I'd appreciate some help here. I've spent a lot of time reading up and understanding the challenges around dates in Excel. So, when I came across this challenge, I thought it would be an easy fix. A bunch of dates in a spreadsheet have become messed up because during a migration one or other of the systems had the wrong locale set causing dates in a UK format to be incorrectly recognized as US Dates. So now there’s a bunch of dates in the sheet, some correct because the high 'day' value seen as an invalid 'month' in US format made the date go in as text (Ironically these dates are correct as they look), but the low day values are recognized as months and form valid dates. For example, the date "7th of Jan" (7/1/2019) is in the sheet as July 1st, and stored as a proper date value i.e. 43647.
Fortunately, in the sheet there was a column whereby the correct month number was entered on its own so I figured I could do a 'DateFixer' macro that simply converts the dates stored as text into date values with a simple datevalue() function and for the cells stored as a date values, convert them to text, check against the column with the month number and swap the Month and Day in the dates that need it then resolve them back as dates values. Sounds straight, forward right?!
Well no. Cell A1 holds a date stored as a string (left aligned). I run the line of code:
Result, 43516, which when formatted as a date shows as 20/02/2019 (right aligned)
But, I put the same steps into a variable and...
changes nothing! Can anyone explain why? And indeed how do I use variable to get the results achieved with the literal reference to Range("A1")?
Bookmarks