I’ve spent countless hours trying to make Excel understand dates and each time I would head over to another report which I am trying to automate for an accountant I had same problem: each beginning date (<12) would get swapped days with months regardless of what method I would use:
regional setting, format date, convert to text, even separate numbers with
RIGHT functions to build new dates from string, I’ve even added functions found on the internet…
I wanted to share my solution for everyone who’s working with multiple reports being somehow transferred over and they include dates…
- Make sure to autofit desired source:
This will work perfectly with *.csv files but you should be able to modify as needed and even being applied to specific range. This is needed as some dates maybe shown as ########
- Convert visible content to text not to work with values:
ASFound.Value = ASFound.Text
ASFound.Value = "." & ASFound.Value
Do not try to apply
ASFound.Value = "." & ASFound.Text – this will still convert dates…
- You can now use your
example.valueto transfer this content over.
Feel free to remove “.” and your date should now be correctly recognized… You can use
REPLACE function. You can use any symbol you desire but I would not recommend “=”, “https://stackoverflow.com/”, “-“.
Alternatively add “.” to dates you are working with (for example to compare stuff) This will force excel to work with a string instead of numbers / dates. Don’t forget to adjust your code to make sure
STRING is in use instead of anything else.
Dim ASpwd As Integer ASpwd = 0 ASwb.Sheets(3).Range("A13").Offset(0, ASpwd).Formula = "=Today()" ASwb.Sheets(3).Range("A13").NumberFormat = "dd/mm/yyyy" Do Until ASpwd = 31 ASwb.Sheets(3).Range("C17").Offset(0, ASpwd).Formula = "=A13-day(A13) 1 " & ASpwd ASwb.Sheets(3).Range("C17").Offset(0, ASpwd).Value = "." & Int(ASwb.Sheets(3).Range("C17").Offset(0, ASpwd).Value) ASpwd = ASpwd 1 Loop
Note that when working with generating dates by adding and subtracting numbers you may end up with dates which will have decimal numbers therefore
Hope this will help for those having issues.