date format from mm/dd/yyyy to yyyymmdd

August 13, 2011 at 22:20:36
Specs: Windows XP
I am trying to take one xl sheet to unix database.

Presently the date field contains mm/dd/yyyy where as the format should be in yyyymmdd to fit in unix.

Pls help

See More: date format from mm/dd/yyyy to yyyymmdd

Report •

August 14, 2011 at 11:07:29
I know nothing about Unix databases, but I do know a bit about Excel dates. What I am about to tell you may or may not help with your task, but I'll toss it out there anyway.

Excel stores dates and times internally as numbers, with the Integer portion being the date and the decimal portion being the time (stored as the decimal portion of 24hrs).

1/1/1900 is Day 1, 8/14/2011 is Day 40769

To prove this to yourself, put a date or a date & time in a cell and format the cell as a number.

Now, what actually displays in a cell that contains a date is based on which format you choose, but internally the date is still a number.

So, if you format the cell as Custom...yyyymmdd you will see e.g. 20110814, but internally Excel will still consider it to be 40769.

How the formatting impacts what gets transferred to your Unix database is beyond the scope of my knowledge, but if "strange" things are happening, it may be because of the way Excel stores dates internally.

If you need to force a date to be a text string so that it transfers as 20110814, use the TEXT function:


You can then do a Copy...PasteSpecial...Values to eliminate the TEXT function and retain 20110814 as Text, formatted as General.

If that doesn't transfer correctly, try formatting the cell as Text after doing the Copy...PasteSpecial...Values.

I hope something here helped!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question