Excel Split Date and Time

Excel Split Date and Time
Last Updated on 28/08/2023
Reading time: 2 minutes

In Excel, if you have in a cell with date and time (like the use of the NOW function) it's easy to split them with 2 formulas.

How to split the date and the time from an Excel cell?

  1. Use the INT function

    The INT function will be useful for extracting the date only

  2. Write a subtraction to extract the time

    Then, to remove the date, a basic operation of subtraction will return the time

Your cells have Date and Time

Let's start from the following document where you have in column A Date & 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.

Step 1: Extract only the date

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

=INT(A2)

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 column B to display the format 'Date'.

Step 2: 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 column C to display the cell format to 'Time'

And the final result is

Split Date and Time with Power Query

Have you heard about Power Query? It's the new tool that transforms your data without a formula.

You can find in this step-by-step guide, how to split date and time with Power Query.

Date only extracted

25 Comments

  1. Situ
    21/01/2022 @ 14:59

    Can you please tell me how to split Dec 25, 2019 - 11:17 PM into date in separate column?

    Reply

  2. shiva
    17/07/2021 @ 11:36

    how to split Date from a sentence in excel

    Reply

    • Frédéric LE GUEN
      19/08/2021 @ 07:05

      You must use FlashFill to extract your date from your sentence

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Excel Split Date and Time

Reading time: 2 minutes
Last Updated on 28/08/2023

In Excel, if you have in a cell with date and time (like the use of the NOW function) it's easy to split them with 2 formulas.

How to split the date and the time from an Excel cell?

  1. Use the INT function

    The INT function will be useful for extracting the date only

  2. Write a subtraction to extract the time

    Then, to remove the date, a basic operation of subtraction will return the time

Your cells have Date and Time

Let's start from the following document where you have in column A Date & 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.

Step 1: Extract only the date

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

=INT(A2)

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 column B to display the format 'Date'.

Step 2: 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 column C to display the cell format to 'Time'

And the final result is

Split Date and Time with Power Query

Have you heard about Power Query? It's the new tool that transforms your data without a formula.

You can find in this step-by-step guide, how to split date and time with Power Query.

Date only extracted

25 Comments

  1. Situ
    21/01/2022 @ 14:59

    Can you please tell me how to split Dec 25, 2019 - 11:17 PM into date in separate column?

    Reply

  2. shiva
    17/07/2021 @ 11:36

    how to split Date from a sentence in excel

    Reply

    • Frédéric LE GUEN
      19/08/2021 @ 07:05

      You must use FlashFill to extract your date from your sentence

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *