How to Convert Separate Excel Date and Time Values into a Real Date
Question details
The user needs to merge a date cell and a time cell into a single, calculable datetime value.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Attempting to merge separate date and time columns into a single column for data analysis or accurate timestamping.
- Observed behavior
- Using the CONCATENATE function returns a text string instead of a real date, which prevents the spreadsheet from applying date and time formatting or allowing chronological calculations.
Ensure your separate date and time cells are recognized by the spreadsheet as valid date and time formats rather than plain text.
Use Simple Addition to Combine Date and Time
Because spreadsheet programs store dates as whole numbers and times as decimal fractions, adding them together naturally creates a valid datetime value.
In Excel, dates are stored as sequential serial numbers starting from January 1, 1900, while times are stored as fractional portions of a 24-hour day. By mathematically adding a date cell and a time cell, you preserve the underlying numerical value required for custom formatting.
Select a blank cell where you want the combined datetime to appear. Assuming your date is in cell A2 and your time is in cell B2, type the formula =A2+B2 and press Enter.
Right-click the result cell and choose 'Format Cells'. Navigate to the 'Custom' category.
In the 'Type' field, input your desired datetime format, such as dd/mm/yyyy hh:mm:ss, and click OK to apply.
Use the TEXT Function for Text Output
If you specifically require the combined date and time to be exported or displayed strictly as a text string, use the TEXT function.
Easily Combine Dates and Times with WPS Spreadsheet
WPS Spreadsheet fully supports standard date and time formulas, including the mathematical addition of date and time serial numbers. Enjoy a seamless data processing experience with complete format compatibility.
- 1. Open Your Data File: Launch WPS Spreadsheet and open the file containing your separated date and time columns.
- 2. Combine with a Simple Formula: In an adjacent blank column, enter the formula =A2+B2 to sum the date and time cells together.
- 3. Customize the Cell Format: Press Ctrl+1 to open the Format Cells dialog, select the Custom category, and apply a datetime format like dd/mm/yyyy hh:mm:ss.
Frequently Asked Questions
Why does CONCATENATE ruin my date formatting?
The CONCATENATE function is designed to join text strings. When you use it on dates and times, it converts their underlying serial numbers into text, dropping all formatting and preventing you from using it as a real date for calculations.
How do spreadsheet programs store dates and times?
Spreadsheets store dates as sequential whole numbers (integers) starting from January 1, 1900, and times as fractional decimals representing a portion of a 24-hour day. This mathematical approach is why you can simply add a date and a time together.
Can I combine a date and time into a 12-hour AM/PM format?
Yes. After adding the date and time cells using =A2+B2, right-click the cell, select Format Cells, choose Custom, and enter a format code like mm/dd/yyyy hh:mm AM/PM.
