Sometimes we want to convert date and time format to date only format in excel for example convert 01/29/2019 06:51:03 to 01/29/2019, we can convert format by Formula or Format Settings. The two ways are easy to learn, so you can select one you like to convert date and time format.
Table of Contents
1. Convert Date & Time to Date by Formula
Prepare a table with date and time, the format is MM/DD/YYYY HH/MM/SS.
If we want to convert them to date format, we can follow below steps:
Step 1: In B2, enter the formula
=MONTH(A2) & "/" & DAY(A2) & "/" & YEAR(A2)
Step 2: Click Enter to get the result. Verify that format is changed to date properly, time part is cleared.
Step 3: Drag the Fill Handle down to fill B3. Verify that B3 is changed to date format properly.
2. Convert Date & Time to Date by Format Settings
Step 1: On A2, right click to load menu, select Format Cells.
Step 2: In Format Cells window, under Number tab, in Category list, select Date; then in Type list select one format you want to show the date. Then the date format is displayed in Sample field.
Step 3: Click OK. Verify that previous date and time is changed to date format.
3. Convert Date & Time Format to Date using VBA Macro
Lastly, let’s explore the third method, where we’ll use a VBA macro to automate the conversion process. This method provides flexibility and customization options for handling date and time data.
Press ALT + F11 to open the Visual Basic for Applications Editor.
In the VBA Editor, go to the “Insert” menu and select “Module” to insert a new module.
Copy the provided VBA code above.Paste the copied code into the new module.
Sub ConvertDateTimeToDate()
Dim rng As Range
Dim cell As Range
'Prompt user to select range containing date and time data
On Error Resume Next
Set rng = Application.InputBox("Select the range containing date and time data:", Type:=8)
On Error GoTo 0
'Check if user selected a range
If Not rng Is Nothing Then
'Loop through each cell in the selected range
For Each cell In rng
'Check if cell contains date and time data
If IsDate(cell.Value) Then
'Convert date and time to date only
cell.Value = Int(cell.Value)
End If
Next cell
MsgBox "Date and time format has been successfully converted to date format.", vbInformation
Else
MsgBox "No range selected. Operation cancelled.", vbExclamation
End If
End Sub
Close the VBA Editor and return to your Excel workbook.
Press ALT + F8 to open the “Macro” dialog.
Select the “ConvertDateTimeToDate” macro from the list.
Click “Run” to execute the macro.
Follow any prompts to select the range containing date and time data.
Once the macro finishes running, you’ll receive a message indicating that the date and time format has been successfully converted to date format.
4. Video: Convert Date & Time Format to Date
This Excel video tutorial, we’ll explore three methods to convert date and time format to date. We’ll start by using a formula-based approach, followed by the Format Cells feature, and finally, we’ll utilize a VBA macro.
5. Related Functions
- Excel YEAR function
The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)… - Excel MONTH function
The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. The syntax of the MONTH function is as below:=MONTH (serial_number)… - Excel DAY function
The Excel DAY function returns a day of a date (from 1 to 31).The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)…