date

Z

ziad alsayed

Well-known Member
Joined
Jul 17, 2010
Messages
665
dear all

with the below text data i need to get the year and formatted to appear "yyyy". the issue i am facing is that sometime it is 2 digit and sometimes 4 digits.
Excel Workbook
A
52/1/11
615/03/2010
Sheet1
Excel 2010

appreciate any assistance.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Data > Text to columns, Next, Next, DMY
 
Upvote 0
thanks Shg, is it possible to do with a formula

below is my data
Excel Workbook
AB
1Date textFormula
22/1/201102/01/11
315/3/201015/03/10
427/12/201127/12/11
52/01/11#NUM!
602/1/11#NUM!
Sheet1 (2)
Excel 2010
Cell Formulas
RangeFormula
B2=DATE(RIGHT(A2,4),SUBSTITUTE(MID(A2,FIND("/",A2)+1,2),"/",""),SUBSTITUTE(LEFT(A2,2),"/",""))
B3=DATE(RIGHT(A3,4),SUBSTITUTE(MID(A3,FIND("/",A3)+1,2),"/",""),SUBSTITUTE(LEFT(A3,2),"/",""))
B4=DATE(RIGHT(A4,4),SUBSTITUTE(MID(A4,FIND("/",A4)+1,2),"/",""),SUBSTITUTE(LEFT(A4,2),"/",""))
B5=DATE(RIGHT(A5,4),SUBSTITUTE(MID(A5,FIND("/",A5)+1,2),"/",""),SUBSTITUTE(LEFT(A5,2),"/",""))
B6=DATE(RIGHT(A6,4),SUBSTITUTE(MID(A6,FIND("/",A6)+1,2),"/",""),SUBSTITUTE(LEFT(A6,2),"/",""))
 
Upvote 0
Salam Zyad
try this
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Date Text</td><td style=";">Formula</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">2/1/2011</td><td style="text-align: right;;">02/01/2011</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">15/3/2011</td><td style="text-align: right;;">15/03/2011</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">27/12/2011</td><td style="text-align: right;;">27/12/2011</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">2/1/11</td><td style="text-align: right;;">02/01/2011</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">02/1/11</td><td style="text-align: right;;">02/01/2011</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=DATE(<font color="Blue">IF(<font color="Red">LEN(<font color="Green">MID(<font color="Purple">A2,FIND(<font color="Teal">"/",SUBSTITUTE(<font color="#FF00FF">A2,"/","#",1</font>)</font>)+1,LEN(<font color="Teal">A2</font>)</font>)</font>)=2,2000+MID(<font color="Green">A2,FIND(<font color="Purple">"/",SUBSTITUTE(<font color="Teal">A2,"/","#",1</font>)</font>)+1,LEN(<font color="Purple">A2</font>)</font>),MID(<font color="Green">A2,FIND(<font color="Purple">"/",SUBSTITUTE(<font color="Teal">A2,"/","#",1</font>)</font>)+1,LEN(<font color="Purple">A2</font>)</font>)</font>),SUBSTITUTE(<font color="Red">MID(<font color="Green">A2,FIND(<font color="Purple">"/",A2</font>)+1,2</font>),"/",""</font>),SUBSTITUTE(<font color="Red">LEFT(<font color="Green">A2,2</font>),"/",""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
dear Yahya

thanks this worked perfectly, it took me about one hour but i understood it :).

i like the logic you put.
 
Upvote 0
dear Yahya

check the below it also works
Excel Workbook
ABC
1DateYahhaZiad
22/1/201102/01/201102/01/2011
315/3/201015/03/201015/03/2010
427/12/201127/12/201127/12/2011
52/01/1102/01/201102/01/2011
Sheet2
Excel 2010
Excel Workbook
C
202/01/2011
Sheet2
Excel 2010
Cell Formulas
RangeFormula
C2=DATE(IF(LEN(MID(A2,LEN(SUBSTITUTE(MID(A2,FIND("/",A2)+1,2),"/",""))+LEN(SUBSTITUTE(LEFT(A2,2),"/",""))+3,4))=4,MID(A2,LEN(SUBSTITUTE(MID(A2,FIND("/",A2)+1,2),"/",""))+LEN(SUBSTITUTE(LEFT(A2,2),"/",""))+3,4),20&MID(A2,LEN(SUBSTITUTE(MID(A2,FIND("/",A2)+1,2),"/",""))+LEN(SUBSTITUTE(LEFT(A2,2),"/",""))+3,4)),SUBSTITUTE(MID(A2,FIND("/",A2)+1,2),"/",""),SUBSTITUTE(LEFT(A2,2),"/",""))
 
Upvote 0
It may depend on your regional settings but if the dates are in a valid format for your region you can convert with any mathematical operation which doesn't change the value, e.g. this works for me

=A2+0

format as required
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
How to fix vba delay in runing the date rule
Replies
3
Views
112
Skyybot
Skyybot
M
  • Question Question
Trouble removing formatting in vlookup area
2
Replies
14
Views
310
Peter_SSs
Peter_SSs
S
  • Solved
Date missing leading zero even with date format of dd/mm/yyyy in Excel
Replies
2
Views
524
Surreybloke
S
A
  • Solved
setting a range from Active cell to last cell when Active cell change columns
2
Replies
12
Views
569
jolivanes
jolivanes
T
  • Solved
Executing modules from any workbook
Replies
5
Views
95
Joe4
Joe4

Forum statistics

Threads
1,224,521
Messages
6,179,278
Members
452,902
Latest member
Knuddeluff

Share this page

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