Date and Time conversions

[Darin Kramer]

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 ***

[Bob Phillips]

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" <darin_kramer@hotmail.com> wrote in message
news:unnfgWIqFHA.4088@TK2MSFTNGP15.phx.gbl...
>
> 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 ***

[Darin Kramer]

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 ***

[Bob Phillips]

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

--
HTH

Bob Phillips

"Darin Kramer" <darin_kramer@hotmail.com> wrote in message
news:%23MW9pDJqFHA.1136@TK2MSFTNGP12.phx.gbl...
> 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 ***