I am using Office 2007. I created a macro to be used by many users and it
strips part of the date to be used for file saving. Users have different date
format in their Windows Regional settings.

Are there any vb code I can use to check Regional date format of the pc
running the macro?

In my macro users select a Sunday date from dropdown list and I entered a
formula in next cell to add 1 to it to get Monday date. I then use Monday
date to get YYYY, MM and DD to make up value YYYYMMDD to use in file name.

Example
B2 and B3 are formatted as Date 2001-03-14
B2 selected value - 2009-08-16
B3 calculated value - 2009-08-17
I would like file name date value to be 20090817
I am using file_name_date = LEFT(B3,4) & MID(B3,6,2) & RIGHT(B3,2)

This works OK if short date in Windows is set to yyy-mm-dd but not if it is
dd/mm/yyyy.

Any suggestions would be most appreciated.

Prema

Hi

In VBA use the format function:

file_name_date = Format(Range("B3").Value, "yyyymmdd")

Regards,
Per

On 19 Aug., 23:26, Prema <Pr...@discussions.microsoft.com> wrote:
> I am using Office 2007. I created a macro to be used by many users and it
> strips part of the date to be used for file saving. Users have different date
> format in their Windows Regional settings.
>
> Are there any vb code I can use to check Regional date format of the pc
> running the macro?
>
> In my macro users select a Sunday date from dropdown list and I entered a
> formula in next cell to add 1 to it to get Monday date. I then use Monday
> date to get YYYY, MM and DD to make up value YYYYMMDD to use in file name.
>
> Example
> B2 and B3 are formatted as Date 2001-03-14
> B2 selected value - 2009-08-16
> B3 calculated value - 2009-08-17
> I would like file name date value to be 20090817
> I am using file_name_date = LEFT(B3,4) & MID(B3,6,2) & RIGHT(B3,2)
>
> This works OK if short date in Windows is set to yyy-mm-dd but not if it is
> dd/mm/yyyy.
>
> Any suggestions would be most appreciated.
>
> Prema

Thank you for your help. Your solution works well.
Prema

"Per Jessen" wrote:

> Hi
>
> In VBA use the format function:
>
> file_name_date = Format(Range("B3").Value, "yyyymmdd")
>
> Regards,
> Per
>
> On 19 Aug., 23:26, Prema <Pr...@discussions.microsoft.com> wrote:
> > I am using Office 2007. I created a macro to be used by many users and it
> > strips part of the date to be used for file saving. Users have different date
> > format in their Windows Regional settings.
> >
> > Are there any vb code I can use to check Regional date format of the pc
> > running the macro?
> >
> > In my macro users select a Sunday date from dropdown list and I entered a
> > formula in next cell to add 1 to it to get Monday date. I then use Monday
> > date to get YYYY, MM and DD to make up value YYYYMMDD to use in file name.
> >
> > Example
> > B2 and B3 are formatted as Date 2001-03-14
> > B2 selected value - 2009-08-16
> > B3 calculated value - 2009-08-17
> > I would like file name date value to be 20090817
> > I am using file_name_date = LEFT(B3,4) & MID(B3,6,2) & RIGHT(B3,2)
> >
> > This works OK if short date in Windows is set to yyy-mm-dd but not if it is
> > dd/mm/yyyy.
> >
> > Any suggestions would be most appreciated.
> >
> > Prema
>
>