I have a number of users using the same macro and part of the macro requires the system date
which it has to separate into its component parts (y,m,d).

My macro is based on English/Canada (short format date yy-mm-dd) and all works well except for
some users who, because of another application they use, require English/USA with the short
format as dd-MMM-yy.

I wrote a routine to determine the format of the date by returning the Application.International
settings Date Order & Separator values which I understood would tell me the short date format
and allow me to break out the date and convert the month to a numeric value.

All appeared well except that during my testing (just for "fun") I tried capturing the system
date using SysDate=Date$ vs. SysDate=Date.

Using May 10, 2008 as an example;
I found that using just DATE gave the correct value 10-May-08 but using DATE$ results in
05-Oct-08.

I've looked at Help as well as Googled until my old eyes gave out and realized that I'm over my
head, as I've always been when it comes to Excel's date manipulations!

My question;
Can anyone shed some light on the difference? I mean I see what it's doing, I just don't know
why it wants to flip the Day & Month in Date$

--
Regards
Rob

Today is May 10, 2008 and on my USA system...
Date returns 5/10/2008
Date$ returns 05-10-2008
Both Month(Date) and Month(Date$) return 5
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"RobWN"
wrote in message
I have a number of users using the same macro and part of the macro requires the system date
which it has to separate into its component parts (y,m,d).

My macro is based on English/Canada (short format date yy-mm-dd) and all works well except for
some users who, because of another application they use, require English/USA with the short
format as dd-MMM-yy.
I wrote a routine to determine the format of the date by returning the Application.International
settings Date Order & Separator values which I understood would tell me the short date format
and allow me to break out the date and convert the month to a numeric value.

All appeared well except that during my testing (just for "fun") I tried capturing the system
date using SysDate=Date$ vs. SysDate=Date.

Using May 10, 2008 as an example;
I found that using just DATE gave the correct value 10-May-08 but using DATE$ results in
05-Oct-08.

I've looked at Help as well as Googled until my old eyes gave out and realized that I'm over my
head, as I've always been when it comes to Excel's date manipulations!

My question;
Can anyone shed some light on the difference? I mean I see what it's doing, I just don't know
why it wants to flip the Day & Month in Date$
--
Regards
Rob

Would it not make sense that DATE$ converts the existing data to text, so if
US date 5/10/08 to Date$ in Canada Sys date format it would be 5 Oct 2008.
Whereas using Date give the constant value in the system short date format.

"RobWN" wrote:

I have a number of users using the same macro and part of the macro requires the system date
which it has to separate into its component parts (y,m,d).

My macro is based on English/Canada (short format date yy-mm-dd) and all works well except for
some users who, because of another application they use, require English/USA with the short
format as dd-MMM-yy.

I wrote a routine to determine the format of the date by returning the Application.International
settings Date Order & Separator values which I understood would tell me the short date format
and allow me to break out the date and convert the month to a numeric value.

All appeared well except that during my testing (just for "fun") I tried capturing the system
date using SysDate=Date$ vs. SysDate=Date.

Using May 10, 2008 as an example;
I found that using just DATE gave the correct value 10-May-08 but using DATE$ results in
05-Oct-08.

I've looked at Help as well as Googled until my old eyes gave out and realized that I'm over my
head, as I've always been when it comes to Excel's date manipulations!

My question;
Can anyone shed some light on the difference? I mean I see what it's doing, I just don't know
why it wants to flip the Day & Month in Date$

--
Regards
Rob

The Object browser states Date$ is a String and Date is a Variant

Whatever that means<g


Gord Dibben MS Excel MVP


On Sat, 10 May 2008 20:33:06 -0700, "Jim Cone"
wrote:


Today is May 10, 2008 and on my USA system...
Date returns 5/10/2008
Date$ returns 05-10-2008
Both Month(Date) and Month(Date$) return 5
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"RobWN"
wrote in message
I have a number of users using the same macro and part of the macro requires the system date
which it has to separate into its component parts (y,m,d).

My macro is based on English/Canada (short format date yy-mm-dd) and all works well except for
some users who, because of another application they use, require English/USA with the short
format as dd-MMM-yy.
I wrote a routine to determine the format of the date by returning the Application.International
settings Date Order & Separator values which I understood would tell me the short date format
and allow me to break out the date and convert the month to a numeric value.

All appeared well except that during my testing (just for "fun") I tried capturing the system
date using SysDate=Date$ vs. SysDate=Date.

Using May 10, 2008 as an example;
I found that using just DATE gave the correct value 10-May-08 but using DATE$ results in
05-Oct-08.

I've looked at Help as well as Googled until my old eyes gave out and realized that I'm over my
head, as I've always been when it comes to Excel's date manipulations!

My question;
Can anyone shed some light on the difference? I mean I see what it's doing, I just don't know
why it wants to flip the Day & Month in Date$

Thanks to all who replied.

Using Jim's reply;
Today is May 10, 2008 and on my USA system...
Date returns 5/10/2008
Date$ returns 05-10-2008
Both Month(Date) and Month(Date$) return 5

My results agree with Jim's EXCEPT if I change the Short date format to dd-MMM-yy, then;
DATE = 10-May-08
DATE$ = 05/10/2008 (Unchanged)
Month(Date) = 05
Month(Date$) = 10
I'm assuming therefore that the system date is "engraved in stone" as "mm/dd/yyyy" but that the
short date format is used to
ascertain the components (the separator, position values y,m,d).
Therefore it says that the month is the second value and thus thinks that the month value is 10.

However if I change my system date to May 13th then;
Month(Date$) = 05 - now that's smart (confusing, but smart!)

I'm not sure but I think I've learned something (stay away from Date$).

(who says you can't teach an old dog...)!
Regards
Rob