# How do I separate the date and time in this column?

1. ## How do I separate the date and time in this column?

Hi,
I have a data set that I have pulled this column from and I am struggling to separate the date from the time in a clean way.

When I use the text to column method some of the dates still have a 00:00 time stamp on them towards the bottom.

Does anyone know the best way to separate the data and time in the sheet provided.

Thanks

2. ## Re: How do I separate the date and time in this column?

You have some date/times as text values, and some as numeric values, though the ones that are numeric have the month and day transformed. You can use this formula to get the date (e.g. in B2):

=IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE(MID(A2,7,4),LEFT(A2,2),MID(A2,4,2)))

and this one in C2 to get the time:

=IF(ISNUMBER(A2),MOD(A2,1),--MID(A2,FIND(" ",A2)+1,10))

Format C2 as a time in the style you prefer, then copy both formulae down to the bottom of your data.

Hope this helps.

Pete

3. ## Re: How do I separate the date and time in this column?

Simply format the date column in the format that you want, for example Short Date or Custom m/dd/yyyy .

The "problem" is: near the end of table (maybe also in the middle), the data is numeric, not text. When you see the separated date as 9/30/2019 00:00, it simply means that the cell contains only a date (or time is truly midnight). A date is simply an integer; time is simply a fraction of 1 (day). If the fraction is zero, it looks like a time of 00:00 (midnight).

Apparently, Text To Columns is smart enough to use a date-only format when the data is text; but not when the data is numeric.

PS.... It is unclear why some of the data is text, in the first place. If your regional date form is d/m/y or anything else other than m/d/y, even TTC might not recognize and format the date correctly for your region unless you choose MDY in the final dialog box where the Finish button is.

4. ## Re: How do I separate the date and time in this column?

For Date

Enter formula in B2 and copy down

=INT(A2) Format as Date: mm/dd/yyyy
For Time

Enter formula in C2 and copy down

=MOD(A2,1) Format as Time: hh:mm:ss

5. ## Re: How do I separate the date and time in this column?

Originally Posted by AlKey
For Date

Enter formula in B2 and copy down

=INT(A2) Format as Date: mm/dd/yyyy
For Time

Enter formula in C2 and copy down

=MOD(A2,1) Format as Time: hh:mm:ss
Hi I have given this a go and it doesn't seem to have worked. I have attached a file showing how I have used the formulaes you have provided.

Thanks

6. ## Re: How do I separate the date and time in this column?

Did you try my formulae from Post #2 - they worked in all cases when I tried them yesterday.

Pete

7. ## Re: How do I separate the date and time in this column?

Originally Posted by Pete_UK
Did you try my formulae from Post #2 - they worked in all cases when I tried them yesterday.

Pete
hi Pete, yes they did. Both worked fine. Much appreciated I just had to close and open a new sheet for some reason.

thanks again

8. ## Re: How do I separate the date and time in this column?

Glad to hear it, and thanks for the rep.

Pete

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1