↑ Return to Date & Time

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

Date_Split_Time_1Let’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).

Date_Split_Time_2So, 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

Date_Split_Time_3To 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)

 




//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js

28 comments

Skip to comment form

  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?

    Thanks for advance.

  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

    1. madamerobina53

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

  10. Anonymous

    Exactly what I needed. Thank you!

  11. madamerobina53

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

  12. Anonymous

    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

    i want to bifurcate this in excel like as given below please help
    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

  16. Anonymous

    It’s really helpful. Thanks

  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!

  23. Mike Wills

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

Leave a Reply

%d bloggers like this: