Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Say I have the following two dates in column 1 of my spreadsheet:

Tuesday, February 12, 2013 10:13 AM
2/12/2013 10:13

When I highlight the column and change the cell formatting to a date type of "3/14/12 1:30 PM" it changes row 2 but not row 1. I quickly discovered that removing "Tuesday, " fixed the issue. In other words, it will properly format "February 12, 2012 10:13 AM" but not "Tuesday, February 12, 2013 10:13 AM".

From there, I started writing a simple formula to remedy the situation. I created a second column and set the formula to

=SUBSTITUTE(A:A,"Tuesday, ","")

This created the following contents:

February 12, 2013 10:13 AM
41317.4256944444

No matter how I reformat the cells it doesn't change the visible values. Is this normal behavior?

share|improve this question

migrated from stackoverflow.com Mar 2 '13 at 9:46

This question came from our site for professional and enthusiast programmers.

add comment

1 Answer

The results of the SUBSTITUTE function are always text values so try adding +0 to the end of the formula to "co-erce" to a date value (and better to refer to a single cell rather than A:A), so in B2 try

=SUBSTITUTE(A2,"Tuesday, ","")+0

or to cope with any day of week try

=IF(ISNUMBER(A2+0),A2,TRIM(REPLACE(A2,1,FIND(",",A2),"")))+0

then format in required date/time format

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.