Strange problem with date format

[Belleke]

For some reason that I can't find out.
With this code the first 3 rows have the US date format, the rest is correct.

Sub Dotchie()
Dim Rng As Range, Dn As Range
Dim St As Date, oEnd As Date, n As Date
Dim p As Long
Dim c As Integer
p = 1
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
St = Application.Min(Rng.Offset(, 1).Resize(, 2))
    oEnd = Application.Max(Rng.Offset(, 1).Resize(, 2))
      ReDim Ray(1 To oEnd - St + 2, 1 To 2)
            Ray(1, 1) = "Datum": Ray(1, 2) = "Bedden in gebruik"
For n = St To oEnd
        p = p + 1
    For Each Dn In Rng
        If n >= Dn.Offset(, 1) And n <= Dn.Offset(, 2) Then
            c = c + 1
        End If
    Next Dn
        Ray(p, 1) = Format(n, "dd/mm/yyyy"): Ray(p, 2) = c
        c = 0
Next n
    Range("D1").Resize(p, 2) = Ray
End Sub

Please advice

 

[Belleke]

Sample file

 

[Chihiro]

That's because dd/mm/yyyy for first three rows can also be valid dates in mm/dd/yyyy format.
10/10/2016
11/10/2016
12/10/2016

 

[Belleke]

Hoi Chihiro, thank you for your reply, but is there a solution?

 

[Chihiro]

Depends on your set up.

Check your setting.

What is default date format used for your system (Region & Language)?


What is the default language set for Excel?

One solution is to format the cell as TEXT and not as date.

 

[Belleke]

Thank you for the reply
Settings:



I try format as text

Thank you

 

[Belleke]

Solved
Solution

[$-en-US]d-mmm-yyyy;@

Have a nice day