What datetime format does Cdate() return in VBA?

user_10160877 0
Viewed 28

I am converting strings into a date format in VBA. The string must first be parsed into date format (whilst still a string), before being cast to a date. E.g I must first transform "091220" to "09/12/20" and then call Cdate()

However, I cannot verify what datetime format is the default for Cdate(). The official documentation here and other sources such as here don't state the format that is returned. Primarily, does Cdate() return a date as dd/mm/yy or mm/dd/yy?

Other questions regarding Cdate() such as here mention the format depends upon the region you are in. I am in the UK.

Through the use of a MWE, I have shown that Cdate() returns it as dd/mm/yy, however I would like to know for sure before I apply this function to the rest of my project.

I attach this MWE below.

public sub date_test()

dim early_date as string '9th June 2020
dim late_date as string '4th July 2020 

early_date = "090620"
late_date = "040720" 

early_date = format(left(early_date,2) & "/" & mid(early_date, 3, 2) & "/" & right(early_date,2), "dd/mm/yy")

late_date = format(left(late_date,2) & "/" & mid(late,3,2) & "/" & right(late_date,2)

msgbox Cdate(late_date) > Cdate(early_date) 'I would like this to return True, which it does

end sub
vba date
1 Answers

You should always handel dates as Date. Try:

public sub date_test()

    dim early_text  as string '9th June 2020
    dim late_text   as string '4th July 2020 

    dim early_date  as date
    dim late_date   as date

    early_text = "090620"
    late_text = "040720" 

    early_date = DateSerial(right(early_text, 2), mid(early_text, 3, 2), left(early_text, 2))
    late_date = DateSerial(right(late_text, 2), mid(late_text, 3, 2), left(late_text, 2))

    msgbox DateDiff("d", early_date, late_date) > 0

end sub
user_35272970
Related