(Solved) – Them pesky dates! MS Excel, VBA or Formulas


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 LEFT and 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…

  1. 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 ########
  2. 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…

  1. You can now use your example2.value = example.value to 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    

Note that when working with generating dates by adding and subtracting numbers you may end up with dates which will have decimal numbers therefore int function.

Hope this will help for those having issues.

Leave a Reply

Your email address will not be published. Required fields are marked *