Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel date problems

Status
Not open for further replies.

shanedavid1981

Programmer
Feb 3, 2005
54
US
Hi everyone,

I seem to have an ongoing problem...

If I get a report from a cgi page into excel (for importing into access), and the cgi page contains dates in american format (mm/dd/yyyy), excel will try and use mm/dd/yyyy as dd/mm/yyyy, rather than change them all around.

So, put simply, the date 03/05/2005 from the cgi (5th March, 2005) translates into excel as 05/03/2005 correctly, but then reads as 3rd May, 2005.

So when I get dates like 03/13/2005 excel doesn't touch it as it's not immediately readable as a date.

Is there a function somewhere I can use to check all the dates and turn them all around to british format?

This has been driving me mad for months. I have tried all ways around it I can think of, changing system locales, excel locales and everything inbetween...

Thanks
 
If you can get the data as text into Excel, there are many ways of achieving what you want. One is using DateSerial along with left/right:

MyDate = DateSerial(right([A1].value,2),left([A1].value,2),right(left([A1].value,5),2))

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.
 
Littlewoman,

Your suggestion will only work if excel treats the date the way David wants from the beginning. The problem here is that Excel interprets the date provided according to the regional standards in Davids computer(mm/dd/yyyy), rather than the format of the data provided (dd/mm/yyyy).

Because of that you need to use left/right on text values to switch place of MM and DD.

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top