Convert a string into date - hour

[Dan89]

Hey guys, I need some help.

I have 2 separated cells with these STRINGS, indicating date and hours:

Apr-14-14 00:31:41
Apr-15-14 00:29:41

I would like to calculate, in a third cell, the difference between the 2 date/hours (in seconds).

How can I convert these 2 string cells into a date + hour format, so that I can compute the difference?

My version of Excel is 2013.

Thank you.

 

[GTO]

But I assume that the OP is using a date setting where month precedes the day, as in the U.S. (?)

Regards

Hi XOR LX,

'APR' and/or the hyphens does/do not get picked up is U.S. Excel. (the date as a semi-string would not meet 'local expectations' with a hyphen, and is not handled)

Mark

 

[XOR LX]

Hi XOR LX,

'APR' and/or the hyphens does/do not get picked up is U.S. Excel. (the date as a semi-string would not meet 'local expectations' with a hyphen, and is not handled)

Mark

Ah, interesting. Thank for that, Mark.

Such inconsistencies as we have to endure across this pond!

Cheers

 

[GTO]

Most definitely Cheers

 

[Rick Rothstein]

Why not just:

=A2-A1

?

'APR' and/or the hyphens does/do not get picked up is U.S. Excel. (the date as a semi-string would not meet 'local expectations' with a hyphen, and is not handled)

Such inconsistencies as we have to endure across this pond!

@XOR LX,

Does "=A2-A1" really work for you in your given Locale? Just out of curiosity, what about this (the formula I posted in Message #6), does it work for you or not?

=SUBSTITUTE(SUBSTITUTE(A2,"-"," ",1),"-",", ")-SUBSTITUTE(SUBSTITUTE(A1,"-"," ",1),"-",", ")

 

[XOR LX]

@XOR LX,

Does "=A2-A1" really work for you in your given Locale? Just out of curiosity, what about this (the formula I posted in Message #6), does it work for you or not?

=SUBSTITUTE(SUBSTITUTE(A2,"-"," ",1),"-",", ")-SUBSTITUTE(SUBSTITUTE(A1,"-"," ",1),"-",", ")

Both work, providing of course I amend the strings given to:

14-Apr-14 00:31:41 and 15-Apr-14 00:29:41

(formatting as text, of course.)

Regards

 

[barry houdini]

This version should convert in both UK and US

=SUBSTITUTE(MID(A1,5,99),"-","-"&LEFT(A1,4))+0

 

[XOR LX]

This version should convert in both UK and US

=SUBSTITUTE(MID(A1,5,99),"-","-"&LEFT(A1,4))+0

Yes, but the interesting point seems to be that, within the correct locale, it is not necessary to first perform any such conversions on the equivalent "Little-endian" version of the given text.

Regards

 

[Rick Rothstein]

Both work, providing of course I amend the strings given to:

14-Apr-14 00:31:41 and 15-Apr-14 00:29:41
(formatting as text, of course.)

Okay, so that means the "mmm d, yy" or "mmm d, yyyy" format is only recognized on my US system... good to know. Thanks.

This version should convert in both UK and US

=SUBSTITUTE(MID(A1,5,99),"-","-"&LEFT(A1,4))+0

Thanks for the "universal" format. Briefly playing around with this format shows you can reduce your formula by 4 characters as this appears to also work...

=SUBSTITUTE(MID(A1,5,99),"-",LEFT(A1,4))+0

An alternate way to perform the same rearrangement of date parts (the way your formula does) is like this...

=(MID(A1,5,2)&LEFT(A1,3)&RIGHT(A1,13))+0

It trades your SUBSTITUTE function call for a RIGHT function call... would I be wrong in assuming that the RIGHT function is less "expensive" than the SUBSTITUTE function?

 

[XOR LX]

Of course, all this could be avoided if an adherence to logical thought re date formats prevailed universally

 

[BrianMH]

Okay, so that means the "mmm d, yy" or "mmm d, yyyy" format is only recognized on my US system... good to know. Thanks.

There are things you don't know?

It's always nice to see something new.