Confusion about dates in VBA

[rubikscube991]

Hey hii experts Please help with below code.

I am not able to understand below problem please help!
If i write...
msgbox Format(Date, "mm/dd/yyyy") result comes as : 07/23/2018

If i write....
msgbox Format(#7/20/2019#, "mm/dd/yyyy") result comes as : 07/20/2019

but for below line it gives a TRUE condition why?

If Format(Date, "mm/dd/yyyy") > Format(#7/20/2019#, "mm/dd/yyyy") Then

pls help!

 

[Marc L]

Hi !

Just normal as Format function returns text …​

 

[rubikscube991]

Thankyou Mark for attention.

I am asking for a solution here. How do i fix this issue because 23 july'18 is not bigger than 20 July'19.

 

[vletm]

rubikscube991
if 'dates' are using as TEXT
a) '2018/07/23' and '2019/07/19', which is bigger?
b) '23/07/2018' and '19/07/2019', which is bigger?

Answer to Your question: '23..' is bigger than '20..' .

 

[rubikscube991]

Mr.vletm why are you talking like this?

You could have just said that there is correction in syntax, i really did not know that i have to write dates this way in vba Format(7/20/2019, "mm/dd/yyyy")

 

[vletm]

rubikscube991
if You compare dates with text-format,
then use format(Your_date,"yyyy/mm/dd").
It compares like
which is bigger year?
which is bigger month?
which is bigger day?

if You use format(Your_date,"mm/dd/yyyy") then
it compares like
which is bigger month?
which is bigger day?
which is bigger year?

I would use function text instead of format, if still challenges.

 

[Marc L]

I am asking for a solution here. How do i fix this issue because 23 july'18 is not bigger than 20 July'19.

As a date can't be a text, the easy solution at child level logic is
of course to not use the VBA function Format (as text, your bad)
but just values in a Date type (as numeric) in order to compare them …
​

 

[Chihiro]

Instead of using text, use date data type. As others have pointed out.

You can use CDate() to coerce string date into numeric date value.

 

[klimbo13]

I have a problem with a VBA form. the code in question gets a date from a cell in the worksheet and displays it in a text box input. (theadate) it displays dd/mm/yyyy The user has the option to change it via a tickbox (thedate) then they change it and then when they press the command button the code saves the new date in the cell. it saves mm/dd/yyyy its confusing the dates because of the inane americans!

 

[vletm]

klimbo13
Challenge ...
It's true that dates could be sometimes challenge,
(hint) but You should learn to use Excel as it works.
Sometimes that needs longer code.