At work we are using Office 365 Excel's file as a booking system. It has multiple tabs for each site where each record has a booking date. There is problem of date formatting - basically the default locale is set to "mm/dd/yyyy" however the date is displayed in "dd/mm/yyyy". When people add rows manually (normally booking slots are generated automatically for each day) and just type in date in a wrong format instead of copying date value from the adjacent cell it displays right, but the cell value in the top bar is different, but when opening this file in the Desktop App it does not see this as different values at all. Only when applying filter, there are dates, and string date values you can filter by. This causes some of the dates not being picked up by the macros while creating reports, or importing data based on the date.
I've been thinking of writing an utility macro that would sanitize all dates based on the dates up and down to the current date, however I am not sure if this is the best way to go. I don't think I can just change the locale settings for all users as for what I read in docs this will make changes only to the single user settings and I am not really sure how this will affect overall functionality of whole system. Is there any way it can be done rather more easily than parsing this massive file or manually finding this dates?
It is a real pain as this file was designed long time before I came to the team and now I am trying to make this less error prone.
Thanks for any clues in advance!
Real dates are numeric. So you can check with IsNumeric
If IsNumeric(Range("A1").Value2) Then
Debug.Print "date"
Else
Debug.Print "string"
End If
Note that you need to check .Value2 not .Value