DATEVALUE("10/30/2009") Fails with #VALUE! Error

[jwarthman]

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.

Thanks!

 

[macropod]

Hi jwarthman,

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.

 

[jwarthman]

Hello,
Thanks! But actually, my source data is in a column that's formatted as TEXT, not as a date/time.

That's why I'm interested in using DATEVALUE to convert from text to an actual date/time value.

Enjoy!

Jim