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

Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)
Processor: Intel

Hello, <br>
I need to manipulate some date/time strings that I'm importing from an external source. The strings look like this: <br><br>10/30/2009 3:19:08 PM <br><br>I need to separate date &amp; time, and use subtotals etc. on change of date &amp; change of hour. <br><br>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. <br><br>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. <br><br>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. <br><br>All help appreciated. <br><br>Thanks!
0
jwarthman
2/14/2010 8:51:19 PM
mac.office.excel 1146 articles. 0 followers. Follow

2 Replies
676 Views

Similar Articles

[PageSpeed] 40

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.


-- 
Cheers
macropod
[Microsoft MVP - Word]


<jwarthman@officeformac.com> 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.
>
> Thanks! 

0
macropod
2/15/2010 11:08:19 AM
Hello, <br>
Thanks! But actually, my source data is in a column that's formatted as TEXT, not as a date/time. <br><br>That's why I'm interested in using DATEVALUE to convert from text to an actual date/time value. <br><br>Enjoy! <br><br>Jim
0
jwarthman
2/17/2010 4:23:53 AM
Reply:

Similar Artilces:

DATEVALUE("10/30/2009") Fails with #VALUE! Error
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hello, <br> I need to manipulate some date/time strings that I'm importing from an external source. The strings look like this: <br><br>10/30/2009 3:19:08 PM <br><br>I need to separate date &amp; time, and use subtotals etc. on change of date &amp; change of hour. <br><br>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. <br><br>...