Split Time and Date

If you have in a cell a time and a date (like the use of the function NOW) it's easy to split them in 2 different cells.

Split the date

Let's start from the following document where you have in column A data with date and time

We want to split

  • The date in column B
  • And the time in column C

To perform this separation, we must keep in mind that 1 represents 1 day.

Time is a fraction of a day, for example, 0.33333 is 8 hours (1/3 of a day). Have a look at this article about the Date and Time format.

Extract only the date

So, to extract the date, you just have insert your value in column A in the INT function.


INT means integer, so you extract the whole part of the cell in column A. And the whole number is the date (the decimal part is the time).

It is possible that you need to change the format of the 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.


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

And the final result is

Related posts

Permanent link to this article: https://www.excel-exercise.com/split-time-and-date/


Skip to comment form

    • Roderic on 28/06/2020 at 13:44
    • Reply

    Does not work for me. I get a VALUE error

    1. It's certainly because the content of your cell is not a date.
      You can check that with this logical test = ISNUMBER(A1)

    • Jill on 12/06/2020 at 13:22
    • Reply

    Hey I want to seperate * 02/02/2020 03:00 AM - 03:59 AM this format into 2 seperate column date and time

    • Prakash on 09/01/2020 at 05:09
    • Reply

    If you want the time to be separated, use -

    This will separate the time. and then for date, copy the date-time column and paste it, and change the format to only show the date

    • Selvi on 30/12/2019 at 09:42
    • Reply

    13-11-2019 09:04:37
    help me split this into date and time separately please

    1. That's what the article describe

    • naseer khan on 24/09/2019 at 08:32
    • Reply

    Thanks a lot Buddy 🙂

    • gloshan kumar on 28/08/2019 at 14:14
    • Reply

    hi i am getting error ( #VALUE!) while applying INT

    Sample given below

    Actual Pickup Time
    06-19-2017 22:39
    06-12-2017 20:22

    1. It's because the contained of the cells are text and not date. Try one of your date +1 ; you will return an error too.

    • Kay on 06/07/2019 at 10:23
    • Reply

    The column with dates and time, has text and number . Tried INT unsuccessfully

    Contract Creation Date
    29/01/2018 11:06:47
    31/01/2018 11:06:28
    20/12/2018 12:33:33
    31/05/2018 15:30:17
    17/09/2018 12:13:19
    26/02/2018 13:10:03
    29/03/2017 16:12:13
    31/01/2018 17:15:02

    • Joe on 25/01/2019 at 23:35
    • Reply

    Hi there,

    I tried doing this with date/time cells in this format, 2018-11-15 17:09:42 PST, and all I get is #VALUE. I changed the cell from text to number, and nothing changed. I have 2600+ cells to change, and don't relish having to do it one-by-one. Any help would be greatly appreciated. Thanks

    1. No easy to reply without the file.
      Can you send me few rows in an excel file to contact@excel-exercise.com

    • Demi on 05/12/2018 at 03:27
    • Reply

    I simply wanted to thank you so much all over again.
    I do not know the things that I could possibly have sorted
    out without the opinions discussed by you concerning such field.
    It absolutely was an absolute scary problem in my circumstances, but
    being able to see the very well-written avenue you dealt with it forced me to
    weep for contentment. I am happy for this support and thus hope that you know what a powerful job you were doing educating the others via your webpage.
    Probably you haven't met all of us.

    • Friend on 27/06/2018 at 09:58
    • Reply

    First time i got this information, its saved my lot of times

  1. Thank you so much for this information, you really saved me some time.

    • Ivan on 08/02/2018 at 20:00
    • Reply

    YOU ARE THE BEST, THANK YOU FOR THIS INFORMATION!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Leave a Reply

Your email address will not be published.

report this ad