Date and Time conversions
 

Howdie,

I have a single column of data containing dates AND times. It is of two
types, either :
3/16/2004 13:06:52 or
2005/07/06 03:43:34 AM or

The first type is thus m/dd/yyyy, or it could be mm/dd/yyyy.
Second type is always yyyy/mm/dd

I am only interested in the dates (ie not the time) and need them to be
in the same format yyyy/mm/dd.
I tried using the Month, Day and year forumulae, but it gets stuck on
the first type.

Further complication is once I have produced this column of Data with
the dates in - it needs to be looked at by folks in the US, so need
excel to be able to adapt the corrected dates to the persons regional
settings (ie soln needs to be a date, not just a number)

Thanks - appreciate any help

Regards

D

*** Sent via Developersdex http://www.developersdex.com ***

Date and Time conversions
 

Darin,

Try adding = INT(A1) in an adjacent column.

As a date, it should ok in UK or US, just formatted differently.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Darin Kramer" wrote in message
...

Howdie,

I have a single column of data containing dates AND times. It is of two
types, either :
3/16/2004 13:06:52 or
2005/07/06 03:43:34 AM or

The first type is thus m/dd/yyyy, or it could be mm/dd/yyyy.
Second type is always yyyy/mm/dd

I am only interested in the dates (ie not the time) and need them to be
in the same format yyyy/mm/dd.
I tried using the Month, Day and year forumulae, but it gets stuck on
the first type.

Further complication is once I have produced this column of Data with
the dates in - it needs to be looked at by folks in the US, so need
excel to be able to adapt the corrected dates to the persons regional
settings (ie soln needs to be a date, not just a number)

Thanks - appreciate any help

Regards

D

*** Sent via Developersdex http://www.developersdex.com ***

Date and Time conversions
 

Hi Bob,

It does not work with the date format mm/dd/yyyy hh:mm:ss, (gives a
#value! error) but does work with the format the other way around.

Any other ideas?

Thanks

D



*** Sent via Developersdex http://www.developersdex.com ***

Date and Time conversions
 

Are you sure it really is a date then, trying a simple formula of =A1+1,
and check.

--
HTH

Bob Phillips

"Darin Kramer" wrote in message
...
Hi Bob,

It does not work with the date format mm/dd/yyyy hh:mm:ss, (gives a
#value! error) but does work with the format the other way around.

Any other ideas?

Thanks

D



*** Sent via Developersdex http://www.developersdex.com ***