# Split Time and Date

In Excel, the function NOW returns the date and time of your computer. But how do you separate the date and time in two cells distinctness?

## Split the date

Let’s start from the following document which traces the data recording every second.

We want to split the date and time in order to put the date in column B and time in column C.

To accomplish this separation, we must keep in mind that the value of 1 represents 1 day. Time is a fraction of a day, for example, 0.33333 is 8 hours (1 / 3 of a day).

So, to extract the date, you can now get the full value of column A using the INT function (INT returns the integer value of a cell).

= INT (A2)

It is possible that you need to change the format of column B to display the format ‘Date‘.

## Extract the time

To extract the time, simply subtract the data in column A with the entire value calculated in column B.

=A2-B2

Again, it is possible that you have to change the format of the column C to display the cell format to ‘Time

## Exercise

Practice within the file below (Double-clic in the cells to visualize the formulas)

1. ##### Anonymous

Thanks , this information was really useful

2. ##### Anonymous

how to extract only numbers from a cell

3. ##### jack

Assuming that your text data is in A1.
How many days? =INT(RIGHT(A1,LEN(A1)-FIND(“to”,A1)-2))-INT(MID(A1,6,LEN(A1)-FIND(” to”,A1)-3))
How many hours balance? =HOUR(MOD(RIGHT(A1,LEN(A1)-FIND(“to”,A1)-2),1)-MOD(MID(A1,6,LEN(A1)-FIND(” to”,A1)-3),1))
How many Minutes? =MINUTE(MOD(RIGHT(A1,LEN(A1)-FIND(“to”,A1)-2),1)-MOD(MID(A1,6,LEN(A1)-FIND(” to”,A1)-3),1))

Your request is a little confusing in that I don’t really know how you want your answers laid out in your spreadsheet. You can see the formulas above are really close to the same formula but with some tweaks for each question. In each case the Date and Time information must be extracted from the phrase “from 12-FEB-16 12:30 to 14-FEB-16 16:45” and placed in memory by using the functions RIGHT, LEN, FIND, and MID. After that information is available we use the INT function to extract each of the Dates and subtract the later date from the earlier date and place that result in a cell with General format.
Next we copy the formula the to the cell where we want the hours balance, wrap it with the MOD function and then wrap that with the HOUR Function, again we subtract the later time from the earlier time and deposit that result in the cell formatted for General.
Next we copy the formula we just used into the cell formatted for General where we want the “How Many Minutes”, Change the HOUR function to MINUTE function and there is minutes. Note the questions and answers are in separate cells. Good luck

4. ##### Irfan

How to extract the details from the below data :

from 12-FEB-16 12:30 to 14-FEB-16 16:45

How many days ? 2 days
How many hours balance? 4 hrs.
How many minutes? 15 minutes

How can I get from the formula?

5. ##### jack

I am using Excel 2007 and XP on a Core 2 computer but it works the same on another computer that is AMD processor, Excel 2010 and Windows 7. I use either INT or TRUNC to extract the Date and MOD to extract the time. For example =MOD(A1,1) MOD is the function, A1 is the cell with the data and for this the divisor is always 1. INT or TRUNC extract the integer part of the Date/Time number (the part before the decimal) and MOD extracts the piece after the decimal which is the time.

6. ##### Anonymous

Thanks. This helped.

7. ##### Ishan Ahamed

use text format in excel 2007 =text(A2,”DD/MM/YYY”) for date and for time =text(A2,”HH:MM:SS)

8. ##### Anonymous

Come here kid let me kiss you in your mouth. Thank you!

9. ##### Ankur panwar

its not working on excel 2007 as my system is core i3
kindly help

I have the same as you Ankur. I need this help, too.

10. ##### Anonymous

Exactly what I needed. Thank you!

My date broke out but the time column, although I formatted it to time gave me #name? Any idea what I did wrong?

Thanks a ton

13. ##### Anonymous

DATE TIME
21/07/2015 09:47:26
21/07/2015 20:09:47
22/07/2015 09:30:00
22/07/2015 20:28:57
23/07/2015 10:07:27
23/07/2015 20:25:14

DATE IN OUT
21/07/2015 09:47:26 20:09:47

14. ##### Anonymous

Thanks a Lot, it was really helpful

15. ##### XAVIER

Thank you very much, Very helpful

17. ##### EDWARD

this is so helpful! thank you

18. ##### Kamran Ahmed

Dear All, I don’t want to split the date & time, i just want to pick date while using COUNTIF function please any help.

19. ##### Anonymous

Worked beautifully. I needed to concatenate the date from one cell containing a date and time with a time from another cell. Just added the new time to the extracted date using the extraction method above.

20. ##### Anonymous

press cntrl f, then replace all @ with blank space. its done.

21. ##### Anonymous

This is only helpful if it’s in military time, but if it came in with the AM/PM, it doesn’t work!

22. ##### Anonymous

This has been so helpful. Thank you!

Thank You

24. ##### Sigma

This does not work. I really appreciate your effort, but it does not work. Here is what my problem is:
In one cell 2014-11-12 @ 11:40 AM
I am trying to remove the time.

1. ##### Frédéric LE GUEN

Are your sure you have a date (numerical) in your cell. If it’s a Text format, it can’t work of course

2. ##### Anonymous

you can try text to column option which under Data Tab, I am sure that will be suitable for you.

25. ##### Anonymous

Thanks a lot..:)