You might want to put the date and time in the same cell when creating an Excel time log of activities. In most instances, your worksheet will have a column for dates and another for times. However, there are some features and functions in Excel that make it simple to combine date and time numbers in one cell. I’ll demonstrate a couple of quick methods to combine date and time values in Excel in this tutorial.
This Tutorial Covers:
- Using Shortcuts on the Keyboard to Combine Date and Time
- Combining Date and Time Using the Basic Sum Formula
- Using TEXT function, combine the date and time in one cell
- Using CONCATENATE Function
1. Using Shortcuts on the Keyboard to Combine Date and Time:
Let’s say we have a list of student names. Now, using a few simple keyboard shortcuts, we’ll add their assignment submission time and date to a single cell.
The steps to use shortcut on the keyboard to put together date and time in one cell are described below:
Step 1: To input a date, choose cell (B2) and press “CTRL+; (semicolon)”.
Press “CTRL+SHIFT+; (semicolon)” in that column to add time. Your current time will be displayed here.
Step 2: Move the cursor over a cell (B2) to reveal the addition sign (+). To apply the same shortcut to all cells, drag it all the way to the foot of the column.
Step 3: Simply select the “Home” tab, then, in the “Number” Format ribbon, click on this drop-down icon to display the formatting choices, if you want to change the format of your date and time, choose “More Number Formats”.
Step 4: The “Format Cells” box reappears. Choose the “Custom” choice and the appropriate format for this column by clicking here. “dd-mm-yy h:mm AM/PM” has been selected. By putting additional criteria below, the Type area, you can also change the formats. To modify the format, click OK.
The result looks like below:
2. Combining Date and Time Using the Basic Sum Formula:
In the following example, we have a dataset where the “Start Date” and “Start Time” of some tasks names are given. The two values in that column must be combined into one value in the “Start Date and Time” field.
The procedure of combining Date and Time in one cell using basic SUM formula:
Step 1: We will put the cell references for the other two columns in cell (D2) of the “Start Date and Time” column. Thus, the formula will be:
=B2+ C2
The “Start Date” column’s cell reference is (B2) in this example, and the “Start Time” column’s cell reference is (C2). Insert a space before C2.
To obtain the outcome, press ENTER.
Step 2: Now use the autofill handler to fill the remaining cells. The outcome is shown below:
Step 3: Simply select the “Home” tab, then, in the “Number” Format ribbon, click on this drop-down icon to display the formatting choices, if you want to change the format of your date and time, choose “More Number Formats”.
Step 4: The “Format Cells” box reappears. Choose the “Custom” choice and the appropriate format for this column by clicking here. “dd-mm-yy h:mm AM/PM” has been selected. By putting additional criteria below, the Type area, you can also change the formats. To modify the format, click OK.
The result looks like below:
3. Using TEXT function, combine the date and time in one cell:
Let’s demonstrate how the TEXT function can be used to quickly merge date and time in a single cell!
Step 1: Apply the TEXT function to column (D2). After entering the values into the function, the final formula is as follows:
=TEXT(B2,”mm-dd-yy”)&TEXT(C2, ” h:mm AM/PM”)
Where,
- B2 and C2 are the cell values.
- “mm-dd-yy” and ” h:mm AM/PM” are used in format_text. If you’d prefer, you can use various date and time forms.
In order to use the formula, press ENTER. Our date and time have been merged into a single cell.
Step 2: To cover every cell, now pull the “Fill Handle” downward. Our merged date and time are finally in one cell.
4. Using CONCATENATE Function:
One of the crucial Excel functions, the CONCATENATE function enables you to link multiple cell references into a single column in a worksheet.
The steps to combine Excel Date and Time in a single cell using CONCATENATE function are described below:
Step 1: Apply the CONCATENATE with TEXT function to cell (D2). To specify the text formats in this case, we will use the TEXT function. When you enter values into the formula, the final formula is as follows:
=CONCATENATE(TEXT(B2,”mm-dd-yy”),” “,TEXT(C2,”hh:mm AM/PM”))
Where,
- Text1 is TEXT(B2,”mm-dd-yy”). The cell reference was given a specific format using the TEXT function.
- Text2 is TEXT(C2,”hh:mm AM/PM”)
- The date and time numbers are separated by a space (“”) in this formula. To combine the values, press ENTER.
Step 2: To cover every cell, now pull the “Fill Handle” downward. Our merged date and time are finally in one cell.
In the formula, you can include additional texts such as “Date:” or “Time:”. The following is the exact formula:
=CONCATENATE(“Date: “,TEXT(B2,”mm-dd-yy”),” “,”Time: “,TEXT(C2,”hh:mm AM/PM”))
The outcome is shown below:
Short Notes:
- You can get the current date and time by using keyboard keys.
The Number Format option allows you to choose and alter your date and time formatting.
For ready-to-use Dashboard Templates:
