Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

share|improve this question

migrated from Mar 2 '13 at 9:46

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

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


then format in required date/time format

share|improve this answer

Your Answer


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.