Changing Date Format

[keanur]

Hi Excel Community,

good morning, my name is Keanu. Recently i was trying to find a formula that can change the Date format from DD/MM to DD/MM/YY. attached is the sample of the file, the data is based on the current year 2017, however the system only showing Date and Month instead of the full format. I had assigned another column (Date Converter), where i can put my formula. Does anyone can help me on this.
Thanking you in advance!

K

 

[NARAYANK991]

Hi ,

See if this is what you wanted.

Narayan

 

[keanur]

Hi ,

See if this is what you wanted.

Narayan

Hi Narayan,

thank you for speedy respond, it almost hit to what i desired..just a small thing here, the data that i provided was based on DD/MM format while the formula showing MM/DD. would you mind to advise?

thanks,
KR

 

[Hui]

Your data is text ie: B15 is 14/01
Narayan's formula for A15 is =DATEVALUE(B15&"/"&YEAR(TODAY()))
This takes the text from B15 and appends a "/" and the year of today 2017 to it
So:
=DATEVALUE(B15&"/"&YEAR(TODAY()))
is the same as
=DATEVALUE(B15&"/"&2017)
=DATEVALUE(B15&"/2017")
B15 is 14/01
so it is now
=DATEVALUE("14/01"&"/2017")
=DATEVALUE("14/01/2017")

Datevalue() converts that to a Date number which will be 42749
Don't believe me, select A15 and click the button

Now how it is displayed is up to you
Select A2:A15
Press Ctrl+1 and click the Number Tab
Scroll down to Date and select a pre-defined format
or goto Custom and type a Custom Number Format eg: dd/mm/yy

is that clearer?

 

[keanur]

Hi ,

See if this is what you wanted.

Narayan

Thank you so much Narayan!

cheers

 

[keanur]

Your data is text ie: B15 is 14/01
Narayan's formula for A15 is =DATEVALUE(B15&"/"&YEAR(TODAY()))
This takes the text from B15 and appends a "/" and the year of today 2017 to it
So:
=DATEVALUE(B15&"/"&YEAR(TODAY()))
is the same as
=DATEVALUE(B15&"/"&2017)
=DATEVALUE(B15&"/2017")
B15 is 14/01
so it is now
=DATEVALUE("14/01"&"/2017")
=DATEVALUE("14/01/2017")

Datevalue() converts that to a Date number which will be 42749
Don't believe me, select A15 and click the View attachment 47201 button

Now how it is displayed is up to you
Select A2:A15
Press Ctrl+1 and click the Number Tab
Scroll down to Date and select a pre-defined format
or goto Custom and type a Custom Number Format eg: dd/mm/yy

is that clearer?

Thank you Hui, i got it now. its better to go to custom format and change it