Date as text. Need to be able to format it.

Eamonn100

Board Regular
Joined
Nov 12, 2015
Messages
156
I have a date that is text, meaning that when I copy/paste it from the internet it can not be formatted. Does anyone know a formula that can leave it so that it can be formatted?

The dates appears like this, "Oct 31, 2015".

Thanks.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
I have a date that is text, meaning that when I copy/paste it from the internet it can not be formatted. Does anyone know a formula that can leave it so that it can be formatted?

The dates appears like this, "Oct 31, 2015".
Does it appear like that with the quote marks?
 
Upvote 0
You could try:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,3),""),",",LEFT(A1,3)))
 
Upvote 0
No without the quote marks.
Select the cell containing it (or if there are more than one cell, select the column containing the text dates), call up the "Text To Columns" dialog box (Data tab, Data Tools panel), select Delimited on the first dialog page and click the Next button, then uncheck all Checkboxes on the second dialog page an finish off by clicking the Finish button. All of your text dates should now be real dates which you can format with the date or custom format of your choice.
 
Upvote 0
Thanks guys. I'll go with steve the fish's formula and explore Rick's way over the holidays. This Excel is like a good Pandora's box, hahaha.
 
Upvote 0
Thanks guys. I'll go with steve the fish's formula and explore Rick's way over the holidays. This Excel is like a good Pandora's box, hahaha.
If you want to go with a formula solution, this is much simpler...

=0+A1

The cell format may indicate Text... ignore that and just format the cell with the date format of your choice.
 
Last edited:
Upvote 0
If you want to go with a formula solution, this is much simpler...

=0+A1

The cell format may indicate Text... ignore that and just format the cell with the date format of your choice.

Rick I tried that formula but it would not let me format it. I'm just getting #VALUE!. Hope I'm not doing something stupidly simple?
 
Upvote 0
Excel wouldn't understand that on my version presumably because im in the UK. It would be ok for 31 Oct, 2016
 
Upvote 0
Excel wouldn't understand that on my version presumably because im in the UK. It would be ok for 31 Oct, 2016
With all the things Excel does for us automatically, one would think Excel could figure out the date from the month name, a 4-digit year and a day value (positive number less than 32) no matter what order they appeared in. Okay, what about the DATEVALUE function, is it smart enough to figure it out? I can't test it on my system, but does this work on your system...

=DATEVALUE(A1)
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top