CODEWITHSUNDEEP
excel-formula
victor
victor

Reputation: 105

Converting date from general type to date, 9/29/1986 0:00:00 to dd/MM/YYYY CSV file

i have hit wall after trying INT, TIMEVALUE and all other date formatting on the dates in CSV file.

I was able to change few dates using INT and then change the date format but few dates (highlighted in yellow) i am not able to convert to date format. Originally it is string, which i tried changing to Number and Date type before applying formulas but still its not getting formatted correctly.

i have tried MID/LEFT etc. to extract part of it but when joining these parts using "" & "" converts to text and converting it to date resulted in long ##### output, did tried excel advance option ticking Use 1904 date system.

Any help in right direction is much appreciated. i have not found any duplicate question similar to my format, closest i found didnt have time stamp so that formula didnt work either.

file with original date

Upvotes: 0

Views: 290

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

Take a look at your date format. The first digit is either 1 or 2 characters and you need to take that variation into account. the nice thing is based on your data that the days is always 2 digits. this simplifies things a little.

Lets start with the basics and assume your first string of a date is in A2. Let us start simply by striping out the numbers from the text one segment at at time while being generic about the position and number of character. So in order to pull out the number for the month, use the following formula:

=LEFT(A2,FIND("/",A2)-1)

Find will look for the position of the / character in the string and return its number. in this case it should be 2. This means its a single digit month. So we only need to pull 1 digit. In the general sense 1 less than the position of the /.

The next task will be to pull the digits for the day. We can do that using a similar formula. This time lets use MID instead of left. In order to for MID to work, we need to define the starting point. This time the general case of the start point will be the first character after the first /. The other nice part about this is we know the number of characters to pull will always be 2. As such you can use the following formula to pull the month:

=MID(A2,FIND("/",A2)+1,2)

(note if your day digits were not consistently 2 then you would have to measure the number of characters between the two / characters and replace the 2 in the formula with you calculation)

In order to pull the year the process is basically the same as for the month with some minor tweaks. The resulting formula I am suggesting is:

=MID(A2,FIND("/",A2,4)+1,4)

Now the reason I used 4 as the starting position for the find is to make the formula work for the case where days could be a single digit. It the closest the second / can be to the start.

now that you have all that you need to combine it together to make the date. This is where the DATE formula comes into play. It works in the following format: DATE(Year, Month, Day). So now we simply grab each of the individual formula and build the DATE formula which should wind up looking like the following:

=DATE(MID(A2,FIND("/",A2,4)+1,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2))

if you get a date that is just bunch of number format the cell to display the date in the format of your choosing.

POC

Upvotes: 2

Related Questions