Excel Formula to Separate Date and Time into Two Columns
Question details
The user needs to split a combined date-time value or text string into two independent columns using Excel formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing or cleaning combined date-time data into separate fields to facilitate easier reporting, filtering, and data analysis.
- Observed behavior
- The user requires formulas to extract the date portion into one column and the time portion into another, depending on whether the source data is a true date-time value or a text string.
Check your source data to determine if it is formatted as a true Excel date-time value or as a text string. True date-time values are stored as numbers in Excel, which changes the type of formula you need to use.
Use INT and MOD Formulas for True Date-Time Values
This method is best for cells containing actual Excel date-time values. It extracts the integer part for the date and the decimal part for the time.
In Excel, dates are stored as whole numbers (integers) representing the number of days since January 1, 1900, and times are stored as fractional decimals of a 24-hour day. By separating the integer from the decimal, you successfully separate the date from the time.
Select an empty cell where you want the date to appear. Type =INT(A2) (assuming A2 contains your date-time data) and press Enter. This extracts the whole number portion.
In an adjacent empty cell, type =MOD(A2,1) and press Enter. This formula extracts only the fractional portion of the number, which corresponds to the time.
Select the newly calculated cells. Right-click and choose 'Format Cells'. Apply the 'Date' format to the column containing the INT formula, and the 'Time' format to the column containing the MOD formula.
Use TEXTBEFORE and TEXTAFTER for Text Strings
Ideal for date-time data stored as text strings (e.g., '6/2/2025, 7:26 AM') in newer versions of Excel.
Easily Separate Data Using WPS Spreadsheet
WPS Spreadsheet provides powerful functions like INT, MOD, and Text-to-Columns, making it incredibly easy to manage, split, and analyze your date and time data without complicated setups.
- 1. Open your data file: Launch WPS Spreadsheet and open the document containing your combined date and time data.
- 2. Apply calculation formulas: In a new column, enter =INT(A2) for the date. In the next column, enter =MOD(A2,1) for the time.
- 3. Alternative: Use Text to Columns: Highlight your data column, click the 'Data' tab on the ribbon, and select 'Text to Columns'. Choose 'Delimited' and pick Space or Comma to split the data visually.
- 4. Format the cells: Right-click the resulting columns, select 'Format Cells', and apply the correct Date and Time formats.
Frequently Asked Questions
Why do my separated dates and times look like random decimal numbers?
Excel stores dates as sequential serial numbers and times as fractional decimals. When you use the INT or MOD functions, the result defaults to the 'General' number format. To fix this, select the cells, press Ctrl+1 to open the Format Cells dialog, and apply the proper Date and Time formats.
Can I use 'Text to Columns' instead of formulas to separate date and time?
Yes. If your date and time are stored as text and separated by a space or comma, you can highlight the column, navigate to the Data tab, click 'Text to Columns', and choose 'Delimited' to separate them visually without typing any formulas.
Why does the TEXTBEFORE formula return a #NAME? error?
The TEXTBEFORE and TEXTAFTER functions are relatively new and are only available in Microsoft 365 and newer versions of Excel. If you receive a #NAME? error, you are likely using an older version. In that case, use the Text to Columns feature or a combination of LEFT, RIGHT, and FIND functions.
What does the double hyphen (--) do in the TEXTBEFORE formula?
The double hyphen, also known as the double unary operator, coerces a text string that looks like a number or date into an actual numeric value. This forces Excel to recognize the extracted text as a real date or time value rather than just a plain text string.
