2nd Post: DATEVALUE Error - Pls Help!

[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 dates are DEFINITELY in text format, and are not in date (serial number) format.

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!

 

[Bob Greenblatt]

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 dates are DEFINITELY in text format, and are not in
date (serial number) format.

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!

Just for kicks, click on one of those cells and press enter. Does it
convert automatically to a date? If so, then multiply all those text
fields by 1 to force a conversion. Enter a 1 in a cell, then use
edit-copy. then select all the date cells and chose paste-special and
select multiply.

 

[jwarthman]

Thanks, Bob.

No, when I press enter nothing changes, and the date/time is still left-justified in the cell.

I believe the problem is that DATEVALUE doesn't accept a date in mm/dd/yyyy format, when my system preference is set for yyyy-mm-dd.

Honestly, if there is a way other than using the DATEVALUE() function for converting these dates, I'm open to suggestions. I've used the T() function to confirm this is a text field. I've tried various permutations of TEXT() to no avail.

Very frustrating...

Thanks!

Jim

 

[Bob Greenblatt]

Thanks, Bob.

No, when I press enter nothing changes, and the date/time is still
left-justified in the cell.

I believe the problem is that DATEVALUE doesn't accept a date in
mm/dd/yyyy format, when my system preference is set for yyyy-mm-dd.

Honestly, if there is a way other than using the DATEVALUE() function
for converting these dates, I'm open to suggestions. I've used the T()
function to confirm this is a text field. I've tried various
permutations of TEXT() to no avail.

Very frustrating...

Thanks!

Jim

I still don't think you (or I) fully understand what the problem is.
Dates and times ARE dates. You should not have to separate them at all.
The issue is formatting. Excel tries to convertanything it understand as
a date to a date. Your string is a perfectly legitimate date. did you
try to multiply it by 1 as I suggested?

 

[jwarthman]

Hi Bob,
So I tried this:

B2 contains 10/30/2009 03:02:29 PM
In a different cell I typed =1*B2
I received #VALUE! error.

This is further confirmation that the original data is "text", not serial-based date/time info.

I simply need to find a way to convert this text data into a serial-based date/time value.

Thanks!

Jim

 

[Bob Greenblatt]

Hi Bob,
So I tried this:

B2 contains 10/30/2009 03:02:29 PM
In a different cell I typed =1*B2
I received #VALUE! error.

This is further confirmation that the original data is "text", not
serial-based date/time info.

I simply need to find a way to convert this text data into a
serial-based date/time value.

Thanks!

Jim

Is B2 preceded by an apostrophe? You can only see this in the formula
bar. Are you in the US? What are your system settings for country?