Forum Discussion
Date and time combined in data extract...
Hi,
I've extracted some CSV data and the date and time information occurs in the same cell (some cells are empty) in such a way excel cannot recognise it, please see below,
Apr 23, 2021 12:05:39 PM
I can see where the date and time is, is there a formula or macro I could run to split the date and time into separate cells?
Thanks,
Tony.
7 Replies
- Take the date out of a date and time. generic recipe =INT(date) The INT function can be used to extract the date portion of a date that also contains time (i.e., a datetime). Excel uses a system where times are fractional values and dates are serial numbers to handle dates and time. Date and time are used to calculate time.
- Thanks for the responses guys, I went with something simpler, I did four find and replaces, replacing the four digit year and its trailing space with a four digit year and a -, then I used a text function to take the text before the - and the text after the -.
tony9i9i9i Assuming the data is in A1 and there aren't any strange spacing issues in the data,
This will get the date:
=LEFT(A1,FIND(",",A1)+5)+0This will get the time:
=MID(A1,FIND(",",A1)+7,99)+0Set the formatting accordingly.
- Are they texts or true datetime value?
- I'm not sure Harun, I suspect in the CSV extract they have become text, how would I tell for sure?
tony9i9i9i Provided that the timestamp is a real date/time value and let's say it's in A1, then you can use =INT(A1) to extract the date value and =MOD(A1,1) to extract the time value. Format the cells as a date and time respectively.
Riny_van_Eekelen Thanks Riny, but that just returned a value error in both cases.
