• 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.

Date Format Issue

Naresh Yadav

Active Member
Respected All,

I have Data of Date which is stored in text and number format
I am trying to get the result in "DD-MMM-YYYY" format through TEXT Formula
But not getting my expected result

Kindly find attached excel file and suggest a formula that can help me to come out from it

activity_date Result Using Text Function Expected Result
12-01-2017 12-Jan-2017 01-Dec-17
12-02-2017 12-Feb-2017 02-Dec-17
12-04-2017 12-Apr-2017 04-Dec-17
12/13/17 12/13/17 13-Dec-17
12/13/17 12/13/17 13-Dec-17
12/14/17 12/14/17 14-Dec-17
12/15/17 12/15/17 15-Dec-17

regards
Naresh
 

Attachments

  • Date 12..01.xlsx
    9.4 KB · Views: 4
Hi ,

What you are asking for is not correct.

If you see the first 3 items in cells A2 , A3 and A4 , their numeric values are :

42747 , 42778 and 42837

When the Date format is applied to these numeric values , the results are :

12 January 2017 , 12 February 2017 and 12 April 2017

If you want that these should be changed to 1 December 2017 , 2 December 2017 and 4 December 2017 , it means you want the data itself to be changed , not just its format.

Is this correct ?

Narayan
 
Respected Narayan Sir,

Yes,Your observation is correct I want my data itself to be changed to the numeric value

regards
Naresh
 
Excel and date format can be a pain the ass (local formats). So in your original dates the months and the days are inversed. Where does the data come from? I mean, we can up with a lot of formulas, but ... 12/04/2017 may be correct (April 12th or December 4th) , whereas 12/13/2017 can only be the 13th of December.
You need to know for sure what the original date format is.
 
Maybe,

1] Copy A2:A8 to B2:B8

2] Select B2 B8 >> "Text to Columns" >> in step 3 of 3, click "Date" and choose MDY >> Finish
View attachment 48861

Regards
Bosco
Respected Bosco Sir,
this is perfect, I wanted the same result which is offered by you,But couldn't understand that post doing "Text to columns" and and than using text function why I am getting my correct expected result as my data is having mix numerical and text value in the same format
once again thank you so much sir for giving your valuable time

regards
Naresh
 
Maybe,

1] Copy A2:A8 to B2:B8

2] Select B2:B8 >> "Text to Columns" >> in step 3 of 3, click "Date" and choose MDY >> Finish
View attachment 48861

Regards
Bosco
What I intended to aim for, when being sure there is a unique format...
I tried a quicky with Power Query, but was rather disturbed I could not get it correct (while using local format in the change data type step). I did not expected that to happen. So fall back to "legacy" features did the trick, nice.
 
Back
Top