Formula Errors

How to Combine Date and Time Values into One Excel Cell

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to merge separate date and time values (such as 20250305 and 0845) into a single Excel cell, choosing between a text string identifier and a mathematically calculable date-time value.

Product: Excel
Device & OS: not provided
Scenario: Merging data from two separate columns (one for date, one for time) into a combined master column for reporting or calculation.
Observed behavior: The current date and time values may be formatted as generic text or numbers rather than actual Excel serial date values, which can cause leading zeros (like '0845') to be dropped when combined improperly.

Before you start

Before merging your data, determine whether you need the final result to be a calculable date-time format for future mathematical operations, or just a static text string for visual identification.

Solution 1Recommended

Combine as Text and Preserve Leading Zeros

Use the TEXT function to combine numeric representations of dates and times while keeping leading zeros intact in the final result.

When dealing with time formats stored as generic numbers (e.g., 845 instead of 0845), standard concatenation will drop the leading zero. Using the TEXT function forces Excel to format the number exactly as specified before joining it with the date.

Select the destination cell

Click on a blank cell where you want the combined date and time string to appear.

Enter the TEXT formula

Assuming your date is in cell A1 and your time is in cell B1, type the formula: =TEXT(A1,"0")&TEXT(B1,"0000")

Apply and fill

Press Enter to execute the formula. You can then click and drag the fill handle at the bottom-right of the cell to apply this formula to the rest of the column.

Formatting Options: You can add a space between the date and time by modifying the formula to: =TEXT(A1,"0")&" "&TEXT(B1,"0000")

Solution 2

Create a Calculable Date-Time Value

Convert your text or numbers into true Excel date and time serial values first, then add them together to create a fully functional date-time value.

Solution 3

Simple Concatenation for Pre-formatted Text

Use the ampersand (&) operator if your date and time cells are already formatted exactly as you want them to appear.

Combine Data Easily with WPS Spreadsheet

Seamlessly Combine Date and Time Values Using WPS Office

WPS Spreadsheet offers full support for all text and date-time functions, making it incredibly simple to combine, format, and calculate date and time values within large datasets.

1. Open your dataset in WPS: Launch WPS Spreadsheet and open the file containing your separated date and time columns.
2. Enter your combination formula: Click the target cell and type your desired formula (e.g., =A2+B2 for true dates, or =TEXT(A1,"0")&TEXT(B1,"0000") for text).
3. Apply custom formatting: Press Enter, then use the Format Cells shortcut (Ctrl+1) to apply a custom date-time format if necessary.
4. Fill the column: Double-click the fill handle in the bottom right corner of the cell to automatically apply the formula down the entire column.

100% compatible with Microsoft Excel formulas like TEXT, CONCATENATE, DATE, and TIME.Intuitive 'Format Cells' dialog to easily customize how your combined date and time values are displayed.Free, lightweight, and capable of processing massive datasets without slowing down your computer.

Frequently Asked Questions

Why did my time value lose its leading zero when combined?

If the time is stored as a standard number in Excel, the leading zero is dropped because numbers do not inherently carry leading zeros. You must use the TEXT function in your formula, like =TEXT(B1,"0000"), to force the leading zero to appear in the combined text result.

Can I add a space between the date and time when combining them?

Yes. You can insert a space by adding " " (a space enclosed in quotation marks) within your concatenation formula. For example, instead of =A2&B2, use =A2&" "&B2.

Why does adding my date and time with a plus sign (+) give me a #VALUE! error?

This error occurs if one or both of the cells contain text strings rather than actual numeric values or true dates. You cannot perform mathematical addition on text. You need to either use the concatenation operator (&) for text or convert the strings into true date/time formats before adding them.

Excel & Data Problems