VBA: Date serial number

[dave2018]

no matter what i try, i cant get vba to return the serial number of the date.

i want for example, to input this: 6/18/2018 and return this: 43269

in a worksheet, u change the format to general, but how to do this in vba?

Thanks!

 

[JoeMo]

Sub test()
Dim dte As Date
dte = #6/18/2018#
MsgBox CLng(dte)
End Sub

 

[Isaac76]

Sub test()
Dim dte As Date
dte = #6/18/2018#
MsgBox CLng(dte)
End Sub

Dear Joemo, CLng(dte) has solved my frustrating date calculation issues I had in my macro.
Thanks a lot.

 

[JoeMo]

Dear Joemo, CLng(dte) has solved my frustrating date calculation issues I had in my macro.
Thanks a lot.

You are welcome - thanks for letting me know!

 

[shaju]

Sub test()
Dim dte As Date
dte = #6/18/2018#
MsgBox CLng(dte)
End Sub

Can you clarify on the usage of # before and after the date.

 

[JoeMo]

Can you clarify on the usage of # before and after the date.

The hash marks (#) indicate to VBA that what the marks enclose is a date (a number not text).

Sub test()
MsgBox CLng(#6/18/2021#)
'next line generates a run time error for type mismatch
MsgBox CLng("6/18/2021")
End Sub

 

[shaju]

The hash marks (#) indicate to VBA that what the marks enclose is a date (a number not text).

Sub test()
MsgBox CLng(#6/18/2021#)
'next line generates a run time error for type mismatch
MsgBox CLng("6/18/2021")
End Sub

Thank you for the explanation.

 

[JoeMo]

Thank you for the explanation.

You are welcome - thanks for the reply.