Function Problems

How to Separate Date and Time from an Excel Timestamp

Maira Mehtab Oct 12, 2026 870 views

Question details

The user needs to extract or analyze specific date and time components from combined Excel timestamps without manually splitting large amounts of data.

How to Separate Date and Time from an Excel Timestamp

Product: Excel
Device & OS: not provided
Scenario: Analyzing time-series data or timestamps by extracting specific date, time, hour, or day elements for easier reporting.
Observed behavior: Date and time values are combined in a single cell, making it difficult to sort or summarize by just the date or just the time.

Before you start

Verify whether your timestamps are stored as real Excel date/time serial values or as text strings, as this determines the most effective separation method.

Solution 1Recommended

Extract Date and Time Using Excel Formulas

The most reliable method for actual Excel datetime values is using mathematical formulas to extract specific parts of the serial number.

Excel stores dates and times as decimal serial numbers, where the whole number represents the date and the decimal represents the time. You can use standard math formulas to separate them.

Extract the Date

In an empty cell next to your timestamp (e.g., B1), enter the formula =INT(A1) to extract just the integer (date) portion.

Extract the Time

In another cell (e.g., C1), enter the formula =MOD(A1,1) to extract the decimal (time) portion.

Extract the Day of the Month

If you only need the specific day, use the formula =DAY(A1) to extract the day of the month as a simple number.

Apply Formatting

Select the new cells, navigate to the Home tab, and apply Short Date format to the INT results and Time format to the MOD results.

Extract Date and Time Using Excel Formulas

Formatting Required: The INT and MOD formulas will initially return numerical values. You must apply Date or Time number formatting to display them correctly.

Solution 2

Group Date and Time Directly in a PivotTable

If your goal is purely analysis, you can group the combined timestamps inside a PivotTable without permanently altering the source data.

Solution 3

Use Text to Columns for Text-Formatted Timestamps

If the timestamps are stored as text strings with a space between the date and time, the Text to Columns feature is the quickest fix.

Advanced Data Analysis Tool

Efficiently Manage and Analyze Data with WPS Spreadsheet

WPS Spreadsheet provides powerful built-in formulas, advanced PivotTables, and intuitive data processing tools to seamlessly handle complex timestamps and large datasets.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the timestamp data.
2. Apply separation formulas: Type =INT(A1) to extract the date and =MOD(A1,1) to extract the time in adjacent columns.
3. Format the result cells: Right-click the calculated cells, select Format Cells, and apply the appropriate Date or Time format.
4. Use Data tools if needed: Navigate to the Data tab to access Text to Columns or insert a PivotTable for immediate grouping.

100% compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Built-in INT, MOD, and date formulas for flawless data extractionAdvanced PivotTable functionality for quick data grouping and analysisFree, lightweight, and features a familiar tabbed interface

Frequently Asked Questions

Why do I get a random number when I use the INT or MOD formula?

Spreadsheet software stores dates and times as serial numbers (e.g., 44197.5). To see the actual date or time, you must change the cell's number format to Date or Time via the Home tab or by pressing Ctrl+1.

Can I separate date and time using Flash Fill?

Yes. If your timestamps are formatted consistently, you can manually type the separated date in the adjacent column and press Ctrl+E to trigger Flash Fill. However, formulas are generally more dynamic if the original data changes.

What should I do if Text to Columns overwrites my data?

Text to Columns pushes the separated data into the adjacent columns to the right. To prevent overwriting, insert one or two blank columns next to your timestamp column before executing the tool.

Excel & Data Problems