Please help with formula

Pat1009

Active Member
Joined
Jun 4, 2015
Messages
264
Hello Board,
I am trying to figure out a formula that will convert this... Tue Mar 21 into 3/21/2017
The Tue Mar 21 part can be anything, such as....
Mon Mar 6
Wed Mar 15
Thu Apr 13
and so on.
Just need to convert this type of date to the long type.

<tbody>
</tbody>
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Is 'Tue Mar 21' a formatted date or text? Test the cell with =ISNUMBER(A1) where A1 houses the date. What is the result?
 
Upvote 0
G
H
1
Jan
1
2
Feb
2
3
Mar
3
4
Apr
4
5
May
5
6
Jun
6
7
Jul
7
8
Aug
8
9
Sep
9
10
Oct
10
11
Nov
11
12
Dec
12

<tbody>
</tbody>

Assuming that the date is text.
With the date text in A1 and table of months in G1:H12. Format the cell to the date format you want.
Code:
=DATE(YEAR(TODAY()),VLOOKUP(MID(A1,SEARCH(" ",A1)+1,3),G1:H12,2,0),RIGHT(A1,2)*1)
 
Upvote 0
Assuming you are using a system that expects dates to be in the format mm/dd/yyyy here is another option:


Excel 2013
AB
1Tue Mar 213/21/2017
2Mon Mar 63/6/2017
3Wed Mar 153/15/2017
4Tue Mar 213/21/2017
Sheet1
Cell Formulas
RangeFormula
B1=0+MID(A1,FIND(" ",A1)+1,10)
 
Upvote 0
FormR, that worked, after changing it to =0+MID(B1,FIND(" ",B1)+1,10)
But when I copy the formula down many rows, for all the rows under the first one, it don't work right.
Tue Mar 21 in cell B1 convert in cell C1.
All beneath C1 does not convert correctly.
We are close.
 
Upvote 0
All beneath C1 does not convert correctly.

Post a few examples of what is in those cells and let us know what result you got when you tried the suggested formula.
 
Upvote 0
After looking closely, this works.
Code:
=0+MID(B1,FIND(" ",B1)+1,10)
Code:

<tbody>
</tbody>
 
Last edited:
Upvote 0
After looking closely, this works. =0+MID(B1,FIND(" ",B1)+1,10)

<tbody></tbody>

Great, thinking about it a little more, I think it can probably be simplified to:

=0+MID(B1,5,6)
 
Upvote 0

Forum statistics

Threads
1,213,585
Messages
6,114,520
Members
448,575
Latest member
hycrow

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