Combine date and time
This tutorial shows how to combine a date and time in one cell using an Excel formula or VBA
Example: Combine date and time
|
This formula uses the Excel TEXT function to format the date and time, that are captured in separate cells, into the nominated formats and concatenate the two by using the (&) sign. In this example we have added a space between the date and time by using &" "&.
|
Sub Combine_date_and_time()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'combine date and time into one cell
ws.Range("D5") = Application.WorksheetFunction.Text(ws.Range("B5"), "dd/mm/yyyy") & " " & Application.WorksheetFunction.Text(ws.Range("C5"), "hh:mm:ss")
ws.Range("D5") = Application.WorksheetFunction.Text(ws.Range("B5"), "dd/mm/yyyy") & " " & Application.WorksheetFunction.Text(ws.Range("C5"), "hh:mm:ss")
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Date Reference: Select the cell reference that captures the date by changing the cell reference ("B5") in the VBA code.
Time Reference: Select the time reference that captures the time by changing the cell reference ("C5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the date and time, in separate cells, that you want to combine by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Date Reference: Select the cell reference that captures the date by changing the cell reference ("B5") in the VBA code.
Time Reference: Select the time reference that captures the time by changing the cell reference ("C5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the date and time, in separate cells, that you want to combine by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
EXPLANATION
This tutorial shows how to combine a date and time in one cell using an Excel formula or VBA.
Both the Excel formula and VBA use the TEXT function to format the date and time into the nominated formats and concatenate them to return both the date and time in one cell.
FORMULA
=TEXT(date,"dd/mm/yyyy")&" "&TEXT(time,"hh:mm:ss")
=TEXT(date,"dd/mm/yyyy")&" "&TEXT(time,"hh:mm:ss")
ARGUMENTS
date: The date that you want to combine with the time in one cell.
time: The time that you want to combine with the date in one cell.
date: The date that you want to combine with the time in one cell.
time: The time that you want to combine with the date in one cell.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Add months to date | How to add months to a date using an Excel formula and VBA | |
| Subtract months from date | How to subtract months from a date using Excel formulas and VBA | |
| Convert number in ddmmyyyy order to date | How to convert number in ddmmyyyy order to date using an Excel formula and VBA | |
| Convert number in yyyymmdd order to date | How to convert number in yyyymmdd order to date using an Excel formula and VBA | |
| Convert date to month name | How to convert a date into a month name using an Excel formula and VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| TEXT Function | The Excel TEXT function returns a numeric value as text, in a specified format |