Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
check date format in regional setting
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
check date format in regional setting
Hi
In VBA use the format function: file_name_date = Format(Range("B3").Value, "yyyymmdd") Regards, Per On 19 Aug., 23:26, Prema 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
check date format in regional setting
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
number format in regional setting vista not carried in excel2007 | Excel Discussion (Misc queries) | |||
Format date with specific regional format | Excel Worksheet Functions | |||
Check the "Windows Regional Languages" setting? | Excel Programming | |||
Date not converting to regional format | Excel Programming | |||
Change the low date range regional setting. | Excel Discussion (Misc queries) |