One of the problems that often comes up when working with Excel programmatically is that dates are often converted to American date format automatically. So for instance you might open a csv file with the value ’07/11/2016′ in dd/mm/yyyy format only for Excel to convert it to mm/dd/yyyy format – i.e. change 7th November 2016 to 11th July 2016. This occurs even when Windows date format is set to dd/mm/yyyy.
Sometimes this can be difficult to spot because the dates you happen to be testing don’t get incorrectly converted. For instance 25/12/2016 will remain Christmas day because 12/25/2016 isn’t a valid date.
Fortunately at least Excel is consistent with this and you can process a date value correctly into a date data type before working with it.
Here’s a function that does this.
Function FixCoercedDate(DateValue As Date) As Date
‘Fixes an Excel coerced date by checking the day 12 or less mm/dd, 13 onwards dd/mm
Dim intDay As Integer
Dim dteRtn As Date
intDay = Day(DateValue)
If intDay <= 12 Then
dteRtn = CDate(Format(DateValue, “mm/dd/yyyy”))
Else
dteRtn = CDate(Format(DateValue, “dd/mm/yyyy”))
End If
FixCoercedDate = dteRtn
End Function