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

Date_Split_Time_1Let'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 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). Have a look at this article about the Date and Time format.

Extract the date

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

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


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


Related posts

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


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