Inside The Web
    Facebook Twitter Instagram
    Inside The Web
    • Office
      • Excel
      • Ms Word
      • OneDrive
      • Google Docs
      • Google Sheets
      • Google Drive
    • Email
      • Gmail
      • Outlook
      • Emal Etiquette
    • Office Etiquette
    • Productivity
    Inside The Web
    Home»Office»Excel»How to Separate Date and Time in Excel

    How to Separate Date and Time in Excel

    Asmi DhakalBy Asmi DhakalAugust 30, 2023 Excel 5 Mins Read

    Excel substitutes for a good task manager. You can maintain records like total working hours and attendance sheets. Additionally, you could even manage time-sensitive data such as delivery dates and times.

    When working with such records, you may find the need to separately enter dates and times to better analyze your data. If your spreadsheet contains date and time values in a single column, fret not as there are multiple methods you can use in Excel to separate these values.

    Using INT Function

    The INT function in Excel returns the integer value of the given reference or data. In date and time, this formula separates the dates from the time. After extracting the date, on a different cell, you can subtract the date value from the original cell containing, the date and time. This will then result in your time.

    The syntax for the INT function is pretty simple. It only takes in one reference as an argument. Here is the format you need to follow when using the INT function in a formula:

    =INT(number)

    In this sheet, I have 12 date and time values in the range A2:A13. Here’s how I will be using the INT function to separate date and time values:

    Step 1: Apply the Formula

    Firstly, let’s use the INT function to extract the date value. We will then subtract the original cell from the extracted result.

    1. In cell B2, Enter the following formula:
    =INT(A2)
    Use INT function to extract Date Values
    1. Use Flash Fill to apply the formula to the rest of the cells.
    2. In cell C2, enter the following formula:
    =A2-B2
    Subtracting two cells in Excel

    Step 2: Change Format to Date

    The INT function returns your values in the number format. Our next step will be to change the cell format to date and time format.

    1. Select range B2:B13 and head to the Home tab.
    2. From the Number section, select the General fly-out.
    3. Choose Short Date.
      Convert to Short Date Excel
    4. Select range C2:C13.
    5. Click on the General fly-out menu in the Number section.
    6. Choose Time.
      Convert to Time Excel

    Use Flash Fill

    You can also use Flash Fill to automatically extract date and time values in Excel. As Excel uses smart AI, if you manually enter date values on the adjacent cell, it will recognize the pattern. Select the remaining cells under the new column and use Ctrl + E. This will automatically paste the remaining values in the selected cells.

    Ctrl E in Excel

    You can use the same technique to separate the Time values from the source column.

    Use Text to Columns

    As the name suggests, the text-to-columns tool in Excel is used to separate texts within a cell into multiple columns. You can use this tool in two ways to separate date and time values.

    If your date and time values are separated by a delimiter, say a comma, you can use this as a basis to separate your values. However, as most date and time values do not have such separators, you can choose the fixed-width option. Using this option, you can separate your data that have spaces in between them.

    1. Select the range containing your date and time values.
    2. Head to the Data tab.
    3. From the Data Tools section, click on Text to Columns.
      Text to Columns Excel
    4. In step 1 of 3, choose Fixed Width > Next.
      Text to Columns Fixed Width
    5. Adjust the line under the Preview section. The section represents each column.
      Fixed Width Text to Columns
    6. Select each column and choose Date under Column data format.
      Format Cell as Date
    7. Select Finish.
    8. Select the range with your dates, go to Home, and convert it into Short Date.

    Use TEXT Function inside DATEVALUE and TIMEVALUE

    You can also use the TEXT function to extract date and time values and place them on a different cell. However, the TEXT function converts the results into the text format. You can change the format back to date by nesting the formula inside the DATEVALUE function and inside the TIMEVALUE function for times.

    The TEXT function is pretty simple to use. Here is the syntax this function uses when creating a formula:

    =TEXT(value,format_text)
    • value: Your reference
    • format_text: The format you wish your value to be in. In this example, we will be using the yyyy/mm/dd (YMD) format for dates and hh:mm:ss format for time.

    In this sheet, we have our date and time values in the range A2:A13. Let’s use the TEXT function to extract the date and time values and nest them inside DATEVALUE and TIMEVALUE to format the text values as date and time.

    1. In cell B2, enter the following formula:
    =DATEVALUE(TEXT(A2,"yyyy-mm-dd"))
    Use DATEVALUE and TEXT function in Excel
    1. Use Flash Fill to apply the formula till B13.
    2. Select the range then in the Home tab, convert the range to Short Date.
    3. In cell C2, enter the following formula:
    =TIMEVALUE(TEXT(A2,"hh:mm:ss"))
    Use TIMEVALUE and TEXT in Excel
    1. Use Flash Fill to apply the formula till C13. 
    2. With the range selected, go to the Home tab and convert the range to Time.
      Change Cell Format in Excel

    Use Power Query

    The Power Query is a powerful automation tool. You can use Power Query to split date and time values into two separate columns. After extracting these values in the Power Query editor, you can then close and load your values into an existing, or a completely new sheet.

    1. Select your table/range.
    2. Head to the Data tab.
    3. From the Get and Transform section, click From Table/Range.
      Get data from table or range Excel
    4. If prompted, click OK on the Create Table window.
    5. Click on the top-left of your column and select Date and Time.
      Convert values in date and time format power query
    6. Go to the Add Column tab in the Power Query Editor.
    7. In the Date & Time section, select Date > Date Only.
      Extract Date Only Power Query
    8. In the same section, click on Time > Time Only.
      Extract Time Only Power Query
    9. Go to Home > Close and Load.
      Close and Load Power Query
    Cell Formats Formula
    Asmi Dhakal

      Asmi is a content writer specializing in MS Office. She loves writing Excel-related content to assist other users to learn their way around the program. She started using Excel when she first entered the industry as a marketing intern. Soon, she realized that learning the often-overlooked spreadsheet program was no piece of cake. In her articles, she attempts to brief out complex topics to help beginner to intermediate-level Excel users. Asmi uses Excel for everything, including creating a grocery list to managing her personal finance. She’s a bit dramatic when it comes to the Flash Fill feature; so much so that could write a eulogy out of it. When she’s not working, you’ll find her learning a new recipe, or cleaning up her mess (out of the failed recipe).

      Related Posts

      Excel By Nisha Gurung

      How to Compare Two Columns in Excel Using VLOOKUP

      Excel By Nisha Gurung

      How to Share Excel Files with Multiple Users

      Excel By Nisha Gurung

      How to Filter Based on a List in Excel

      Add A Comment
      Table of ContentsToggle Table of ContentToggle
      • Using INT Function
        • Step 1: Apply the Formula
        • Step 2: Change Format to Date
      • Use Flash Fill
      • Use Text to Columns
      • Use TEXT Function inside DATEVALUE and TIMEVALUE
      • Use Power Query
      • Home
      • About Us
      • Privacy Policy
      © 2025 Inside The Web

      Type above and press Enter to search. Press Esc to cancel.