CODEWITHSUNDEEP
vbadateformatdisplay
neke
neke

Reputation: 85

Date display vs date format VBA

I think that I try to do something what is impossible. I have in range A1:A5 some dates. These cells have to have date format, because I’d like to check if they are bigger or smaller than certain date. Moreover I’d like to display these dates in a range A1:A5 in a following format: yyyy.mm. I think that there is no way to kill two birds with one stone.

Public Sub test()

Range("A1:A5").NumberFormat = "yyyy.mm"
Range("A1:A5") = Format(Date, "yyyy.mm")

If IsDate("A2") Then
    Range("B2") = "OK"
End If

End Sub

Range("B1:B5").NumberFormat = "yyyy.mm" change the cells format on custom (yyyy.mm) but the dates are still displayed in unchanged form (yyyy.mm.dd)

Range (“A1:A5”) = Format(Date, “yyyy.dd”) displays the dates yyyy.mm but the year and month has been changed to 1905.07. Moreover the dates do not have Date format but Custom.

Is is really not possible to display a date like yyyy.mm but in date format (so that in formular bar the date is displayed as dd.mm.yyyy or another way around) to be able to compare them with different dates?

Best regards, Neke

Upvotes: 0

Views: 274

Answers (1)

Slaqr
Slaqr

Reputation: 563

First of all, using points within dates can be a problem, because "2017.05" is interpreted by Excel as a number.

You can reformat the dates into "yyyy/mm" by using something like this:

For Each r In Range("A1:A5")
    r.Value = VBA.Format(r, "yyyy/mm")
Next r

Upvotes: 0

Related Questions