You've probably already got a date & time value in the source cell. For a value in A1, you should be able to use =INT(A1) for the
date and =MOD(A1,1) for the time. With the target cells formatted appropriately for date (mm/dd/yyyy) & time (hh:mm:ss),
respectively, you should get the results you're after.
[Microsoft MVP - Word]
<firstname.lastname@example.org> wrote in message news:59bb2d2b.-1@webcrossing.JaKIaxP2ac0...
> Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hello,
> I need to manipulate some date/time strings that I'm importing from an external source. The strings look like this:
> 10/30/2009 3:19:08 PM
> I need to separate date & time, and use subtotals etc. on change of date & change of hour.
> I thought the best thing would be to separately extract the date and time, then use DATEVALUE() on the date string so I can format
> it as yyyy-mm-dd and do calculations.
> However, no matter what I do, I have not been able to get the DATEVALUE function to work on a date of the format mm/dd/yyyy, even
> when I enclose the date in quotes directly in the formula - let alone trying to extract it from my source data.
> I am in the US, and have verified my region settings, although I use a custom date format on my Mac of yyyy-mm-dd.
> All help appreciated.