Date format issue

[DanielHurtubise]

Hi all,
i have a station where the regional parameters show date as yy/MM/dd,
ie : 05/07/18
When i open it on my station, where dates are shown like yyyy/MM/dd, i
endup with 2018/07/05, which is obviously the wring date.
How can i correct that in about 100 excel files. I will then make sure
all offices run the same schem for regional parameters.

Thanks for your help

 

[Ron Rosenfeld]

Hi all,
i have a station where the regional parameters show date as yy/MM/dd,
ie : 05/07/18
When i open it on my station, where dates are shown like yyyy/MM/dd, i
endup with 2018/07/05, which is obviously the wring date.
How can i correct that in about 100 excel files. I will then make sure
all offices run the same schem for regional parameters.

Thanks for your help

Some things to check:

1. Is the value on Station 1 TEXT or a real date. If the former, changing the
format within Excel to General will do nothing. If the latter, then changing
the format to General will give you a number like 38551, as Excel stores dates
as serial numbers from 1900 (or 1904).

2. What are the settings in the Windows Control Panel for Short Date on both
your machine and Station 1?

3. Are you just "opening" the file on your machine? Or are you importing via
a csv or txt file?


--ron

 

[Dave Peterson]

If you're saving the workbook as a normal .xls workbook, I don't think it has
anything to do with your regional settings...

I'd go back to that first workbook and format the cell as mmmm dd, yyyy (or some
non-ambiguous date format).

I have my doubts what's in that original cell.

If you're saving the file as .Txt or .CSV, then rename it as .txt and when you
open that file, you'll see the text import wizard. From there you can specify
the format of each field--including dates.

But you'll still have trouble with 1900 vs. 2000 dates. You'll make your life
much simpler if you save it with a 4 digit year.