• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Changing Date Format

keanur

Member
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
 

Attachments

  • Data.xlsx
    8.5 KB · Views: 2
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
 
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 upload_2017-11-8_13-9-42.png 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?
 
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
 
Back
Top