Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different regional date settings in reports
Excel 2000
We extract data from an Access database into a spreadsheet. This data includes dates. We operate under NZL regional systems settings (similar to UK) but the report recipients want the spreadsheet sent to them in US formats. That is apparently because the spreadsheet is automatically picked up by their system for analysis, and it is is spitting out our dates. At that end of the process there is apparently no way for the date format to be changed before it is processed. ie manually changing the spreadsheet at their end first. We can produce the desired format by changing our own system settings to US, do the job, send the spreadsheet, then revert the settings back to NZL when we are done - but this is a regular report. That is hardly satisfactory and there must be a way of delivering the requested "format". I would have thought the a date is a date (an underlying value) and the formating is merely a view of that value. Is there a way of presenting the date in our spreadsheet (say as a text string) that would be read at the other end as a date "formatted" [US] in the way they require? Any ideas appreciated. WSF ps not really sure I am making a lot of sense here. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different regional date settings in reports
Piri,
As you say, dates are just numbers and the format is just a view, so there should not be a problem. However, there is an issue with VBA and dates, but it usually occurs for us (non-US) and not the other way around. Are you using VBA at all in these reports? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Piri" wrote in message oups.com... Excel 2000 We extract data from an Access database into a spreadsheet. This data includes dates. We operate under NZL regional systems settings (similar to UK) but the report recipients want the spreadsheet sent to them in US formats. That is apparently because the spreadsheet is automatically picked up by their system for analysis, and it is is spitting out our dates. At that end of the process there is apparently no way for the date format to be changed before it is processed. ie manually changing the spreadsheet at their end first. We can produce the desired format by changing our own system settings to US, do the job, send the spreadsheet, then revert the settings back to NZL when we are done - but this is a regular report. That is hardly satisfactory and there must be a way of delivering the requested "format". I would have thought the a date is a date (an underlying value) and the formating is merely a view of that value. Is there a way of presenting the date in our spreadsheet (say as a text string) that would be read at the other end as a date "formatted" [US] in the way they require? Any ideas appreciated. WSF ps not really sure I am making a lot of sense here. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different regional date settings in reports
If you have Excel 2002 or up, look into format the date cell in one of the
formats marked with an *. From the Format Cells: Except for items that have an asterisk (*), applied formats do not switch date orders with the operating system. NickHK "Piri" wrote in message oups.com... Excel 2000 We extract data from an Access database into a spreadsheet. This data includes dates. We operate under NZL regional systems settings (similar to UK) but the report recipients want the spreadsheet sent to them in US formats. That is apparently because the spreadsheet is automatically picked up by their system for analysis, and it is is spitting out our dates. At that end of the process there is apparently no way for the date format to be changed before it is processed. ie manually changing the spreadsheet at their end first. We can produce the desired format by changing our own system settings to US, do the job, send the spreadsheet, then revert the settings back to NZL when we are done - but this is a regular report. That is hardly satisfactory and there must be a way of delivering the requested "format". I would have thought the a date is a date (an underlying value) and the formating is merely a view of that value. Is there a way of presenting the date in our spreadsheet (say as a text string) that would be read at the other end as a date "formatted" [US] in the way they require? Any ideas appreciated. WSF ps not really sure I am making a lot of sense here. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different regional date settings in reports
I have an application where I assigned different formats to variables
and apply the formats to the cells as needed. It is set up to convert from various country formats to various OS formats. You need just a subset of that, such as. dim dateTimeFmt as String dateTimeFmt = "dd/mm/yyyy hh.mm" Range("A1:C1").NumberFormat = dateTimeFmt Carl. On May 1, 12:02 am, Piri wrote: Excel 2000 We extract data from an Access database into a spreadsheet. This data includes dates. We operate under NZL regional systems settings (similar to UK) but the report recipients want the spreadsheet sent to them in US formats. That is apparently because the spreadsheet is automatically picked up by their system for analysis, and it is is spitting out our dates. At that end of the process there is apparently no way for the date format to be changed before it is processed. ie manually changing the spreadsheet at their end first. We can produce the desired format by changing our own system settings to US, do the job, send the spreadsheet, then revert the settings back to NZL when we are done - but this is a regular report. That is hardly satisfactory and there must be a way of delivering the requested "format". I would have thought the a date is a date (an underlying value) and the formating is merely a view of that value. Is there a way of presenting the date in our spreadsheet (say as a text string) that would be read at the other end as a date "formatted" [US] in the way they require? Any ideas appreciated. WSF ps not really sure I am making a lot of sense here. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regional settings | Excel Worksheet Functions | |||
Regional Settings with Date | Excel Discussion (Misc queries) | |||
regional settings | Excel Worksheet Functions | |||
Excel ignores boot-time regional settings when interpreting a date | Excel Discussion (Misc queries) | |||
Regional Settings | Excel Discussion (Misc queries) |