Excel date problems

[shanedavid1981]

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

 

[01Patrik]

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]

MyDate = DateSerial(Year([Date]), month([Date]), day([Date]))

;-)

 

[01Patrik]

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.