Convert Year to the beginning of the year in dd/mm/yyyy format
I have a column that has years in all sorts of formats due to bad data entry and have been assigned to fix it, I am somewhat new to excel and want to convert dates with only a year entered into a valid format dd/mm/yyyy the formula I have attempted so far is =IF(LEN(N8) = 4, TEXT(1/1/RIGHT(N8,2), "#/#/##"), N8)
where I leave the value alone if its not a year and take the back 2 numbers off the year to try to put it into the format but it doesn't work any advice will help thanks
what sort of different entries are you seeing ?
be useful to know all possible formats
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Like MONTH YEAR, (Example JAN 13) or just a year, if the formula can accommodate the Month if it has one that would be ideal
i think thats going to be difficult as its going to be text and dates mixed
dates are numbers
JAN 2013
JAN 13
1/13
13
could all be the year 2013
I dont know how to do this
Maybe?
=IF(LEN(N8) = 4, TEXT(DATE(RIGHT(N8,2),1,1), "d/m/yy"), N8)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks