CODEWITHSUNDEEP
exceldate
Julia
Julia

Reputation: 176

Converting Timestamp to Date Excel

I have a timestamp column that comes in the format "Aug 01 2016 - 13:46". To format it like a date, because I don't need the timestamp, I'm getting rid of the spaces and the timestamp,

(SUBSTITUTE(LEFT(A2,LEN(A2)-8)," ",""))

and then doing 

DATE(YEAR(RIGHT(C2,4)),MONTH(VLOOKUP(LEFT(C2,3),$H$2:$I$13,2,FALSE)),DAY(IF(ISNUMBER(MID(C2,4,1)),MID(C2,4,2),MID(C2,5,2))))

Where I'm grabbing the right 4 digits as the year, doing a vlookup for the month string, and then getting the middle digits for the day (the IF statement is because occasionally the month will be longer than 4 characters). My result is "1/12/05" Cannot figure out what I'm doing wrong, and would so appreciate any help! Would typically use strftime in R, but the dataset is tiny :)

Thanks

Upvotes: 2

Views: 5879

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40204

(Edit: This answer may not work on versions prior to Excel 2016 as pointed out by @pnuts in the comments below.)

The solution @pnuts gave works fine, but here a shorter, simpler one to consider:

=VALUE(SUBSTITUTE(LEFT(A2,11)," ",", ",2))

This takes the LEFT 11 characters (Aug 01 2016) and then uses SUBSTITUTE to add a comma to the second space (the 2nd " " becomes ", "). Excel is then smart enough to interpret this as a date when applying VALUE.

Now if you aren't guaranteed that it will be exactly 11 characters due to different length month names, one digit day values, or two digit year values (an edge case where the solution by @pnuts would fail), then you can modify it to the following:

=VALUE(SUBSTITUTE(LEFT(A2,SEARCH("-",A2)-2)," ",", ",2))

The only difference here is that we use SEARCH("-",A2) to find where the hyphen occurs and back up two characters from that spot instead of using a hard coded 11.

Using FIND instead of SEARCH works as well. The difference between these two is that FIND is case-sensitive while SEARCH is not. Using DATEVALUE in the formula instead of just VALUE would also work, but the latter saves a few characters.

Upvotes: 2

pnuts
pnuts

Reputation: 59460

Please try: 

=DATE(MID(A2,FIND(" -",A2)-5,5),VLOOKUP(LEFT(A2,3),$H$2:$I$13,2,0),MID(A2,FIND(" -",A2)-7,2))

There may be a much shorter version but I am curious about the different format/s you do not show (and what is in H2:I13) and I have tried to 'respect' your approach.

A shorter version (without the need for a VLOOKUP): 

=DATEVALUE(MID(A2,FIND(" -",A2)-7,2)&"-"&LEFT(A2,3)&"-"&MID(A2,FIND("-",A2)-5,5))

The longer version above uses a lookup table (H2:I13) where the first three characters of the date stamp are converted to a number representing the relevant month. This avoids what might otherwise be complication from length inconsistency in the month indication.

The year and the day are picked out by their relationship to the reference point of a space/dash pair. Relative to that the year will start four characters sooner (and is of length four).

The space/dash pair (just the dash would have been sufficient as a reference point) starts at the twelfth character for the example, as located by: 

FIND(" –",A2)

From this result (ie 12) 5 is subtracted to countback to the start of the year. (Actually the space before the start of the year, but that does not matter). From the space in front of the year, the year is five characters long, this being the length of the string extracted from A2 by: 

MID(A2,FIND(" -",A2)-5,5)

The day (01 for the example) is extracted in a similar fashion and then the three pieces (Year, VLOOKUP result and Day) and fed as parameters into the =DATE function.

The shorter version avoids the need for a lookup table by concatenating, in the middle of Year and Day (extracted in a similar way as for the long version), with the first three characters of the datestamp, obtained from: 

LEFT(A2,3)

surrounded by - on each side.

(see LEFT which is effectively a slightly simplified form of MID as used elsewhere).

The result is a string but in a format recognised by Excel and one that the handy function =DATEVALUE can convert into a Date/Time value.

This would be a lot easier were the data more consistent (ie always three letters for Month).

Upvotes: 3

Related Questions