Problems using DATEDIF() function

[MacroShark]

In my sheet I import dates in the format dd-mmm-yyyy which is the english region format. My problem is that my Windows is set up to Danish region format (dd-mmm-åååå). This causes the DATEDIF() function to fail. The DATEDIF() function works if I go to the Windows region settings and change to English, but I would like to avoid this as it is tedious and it needs to be set back every time after use.

My question is, can I use VBA to help me out with changing the region setting temporarily while the workbook is open and then set back when closing it? Or any other suggestions on how to solve this issue would be highly appreciated.

Thank you

 

[Coding4Fun]

I did not find a way to change this setting but what if you tried to reformat the date within the function.

Sub Test()


Dim myDate1 As Date
Dim myDate2 As Date
Dim myVal As Long


myDate1 = Application.WorksheetFunction.Text(Range("G9").Value, "mm-dd-yyyy")
myDate2 = Application.WorksheetFunction.Text(Range("H9").Value, "mm-dd-yyyy")


myVal = DateDiff("m", myDate1, myDate2)


End Sub

 

[MacroShark]

Excellent! Works perfectly.

Thank you very much for your help.

BR the shark