In one column, I have timestamps in the format Jun 10, 2015 11:36:51 AM
So, to get the date from that column, I created another column and used the formula
=DATEVALUE(CONCATENATE(MID(D2,5,2)," ",LEFT(D2,3)," ",MID(D2,8,5)))
So essentially, this operates as follows -
=DATEVALUE(CONCATENATE("10"," ","Jun"," "," 2015")
=DATEVALUE("10 Jun 2015")
This gives me the result 10-06-2015
Now, in another column, I have obtained dates using VLOOKUP
as follows -
=VLOOKUP(Customers!B2,Customers!B2:B5013:Customers!I2:I5013,8,FALSE)
This column also has timestamps of the format Jun 10, 2015 12:22:48 PM
I applied the same DATEVALUE
formula as above to convert this to date -
=DATEVALUE(CONCATENATE(MID(L2,5,2)," ",LEFT(L2,3)," ",MID(L2,8,5)))
But this time, I'm getting the value 42165
Why is this happening?
I want to find the difference between these 2 dates. Hence I want them in the same format.