New Contributor

# 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

# Re: Date and time combined in data extract...

@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.

# Re: Date and time combined in data extract...

Are they texts or true datetime value?

# Re: Date and time combined in data extract...

@Riny_van_Eekelen Thanks Riny, but that just returned a value error in both cases.

# Re: Date and time combined in data extract...

I'm not sure Harun, I suspect in the CSV extract they have become text, how would I tell for sure?

# Re: Date and time combined in data extract...

@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)+0``

This will get the time:

``=MID(A1,FIND(",",A1)+7,99)+0``

Set the formatting accordingly.

# Re: Date and time combined in data extract...

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.

# Re: Date and time combined in data extract...

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 -.