How to Remove Time from a Date in Excel [12 Ways]

Do you need to remove time from a date in Excel for your analysis? This blog post explains how to use excel commands, functions, power pivot, power query, VBA, and Office Scripts to remove time.

Excel stores dates and times using serial numbers, ddddd.tttttt format. The number of days counting from January 1, 1900. The fraction portion of the serial number is time.

Basic arithmetic operations such as addition or subtraction with date is an essential task for data analysis.  You have to subtract the start date from the end date to calculate the number of weeks or days spent completing the project.

Suppose, you are working with sales data you have to show the MOM – Month Over Month, YOY – Year Over Year, YTD – Year To Date, QTD – Quarter To Date, and MTD – Month To Date Sales in your report. Also, you have to prepare the sales trend to understand how the sales are performed in a period of time. 

Most of the time you receive the date with time in your data file, you need to remove time from a date and analyse the data.

In this article, you will learn a few different methods to remove time from a date in Excel.

This blog post covers how to remove time from a date using the Excel command Find and Replace, Format Cells, Short Date and Long Date. You can use excel functions TEXT, DATE, DATEVALUE and INT to delete time. Discovering the Power Query, and Power Pivot tools to remove time from a date. VBA or Office Scripts code can use in your automation or dashboard to erase time from a date.

Suppose you have a sales data table with the field Invoice Date & Time. You need only the Date for your analysis. So, you remove the time from the date in column B.

Get your copy of the example workbook used in this blog post to follow along!

Remove Time from a Date in Excel with ShortDate

Excel Short Date format is a date format in Excel. It displays dates as numbers in the dd/mm/yyyy format. Long Date format displays the month in the long format. Eg. 02 March 2023.

Select the range of cells B2:B16, Click the drop-down icon in the Number Format commands and select Short Date. You can select the Long Date instead of the Short Date in the Number Format items.

Remove Time from a Date in Excel with Format

You can use excel number format to format the cells containing numerical data to remove time. Excel Number format has multiple formatting options, which allow you to customize the appearance of numerical data.

Select the range of cells B2:B16, Click the Right mouse button and choose Format Cells… to open the Format Cells dialog box.

Choose Date in Category, Select the relevant type *14-03-2012, and then press OK.

Remove Time from a Date in Excel with ShortCut

Keyboard Shortcuts allow you to navigate and perform actions within Excel without using a mouse. CTRL + SHIFT + 3 is the shortcut key for date format in Excel.

Select the range of cells B2:B16 and Press CTRL + SHIFT + 3 to remove time from a date.

Remove Time from a Date in Excel with TEXT function

In this section, you learn how to use TEXT functions to erase the time component from a date. The TEXT function is used to convert numbers to text, format text, change the case of text, extract specific words from a text string and more.

TEXT( value, format_text )

Go to cell E2, type the formula =TEXT( B2, "dd-mm-yyyy" ), and Press Enter. Drag the fill handle to the range E2:E16 to copy the formula.

Remove Time from a Date in Excel with DATE function

The DATE function allows you to enter dates in a specific format, perform add or subtract days, weeks, months, or years to or from a date. The function can also be used to extract date components year, month or day from a date.

DATE( year, month, day )

It returns the excel date-time serial number which represents a date.

Go to cell E2, type the formula =DATE( YEAR( B2 ), MONTH( B2 ), DAY( B2 ) ), and Press Enter. Drag the fill handle to the range E2:E16 to copy the formula.

The Year(), Month() and Day() functions return the year, month and day, respectively, of the given date-time serial number.

Remove Time from a Date in Excel with DATEVALUE function

The Excel DATEVALUE function is like the DATE function but it converts the given text representing a date to a date-time serial number. Later you can format the serial number to a short date or long date using number format.

DATEVALUE( date_text )

Go to cell E2, type the formula = DATEVALUE( B2 ), and Press Enter. Drag the fill handle to the range E2:E16 to copy the formula.

Remove Time from a Date in Excel with FIND and REPLACE feature

In this section, you learn how to extract just the date from a date and time combination using excel basic Find and Replace feature. It helps you to search for specific text, numbers, or formulas in a worksheet and replace them with new values. In excel you can notice a space between date and time. You can search the space between date and time and replace all the text after the space delimiter.

Select the range of cells B2:B16 and press Ctrl + H to open the Find and Replace dialog box.

Type a blank space and an asterisk  * in the Find what: input box and then Press Replace All button.

Remove Time from a Date in Excel with INT function

Excel INT function returns the integer portion of a given number. Excel uses serial numbers to store dates and timings ddddd.tttttt format. INT function returns only the ddddd from the serial numbers, which represents only the date.

INT( number )

Go to cell E2, type the formula =INT( B2 ), and Press Enter. Drag the fill handle to the range E2:E16 to copy the formula.

Remove Time from a Date in Excel with Power Pivot

Power Pivot is an add-in for Excel that enables you to create complex data models for analysis and reporting. It allows you to join data from multiple sources, create relationships between data sets, and perform complex calculations and analyses.

1. Select the range of cells A1:D16 in the Data Sheet
2. Go to Power Pivot Menu, and press the command Add to Data Model to add the data to the power pivot

3. Rename the table name PPData in the Power Pivot editor, select the Add column, type the formula =FORMAT( PPData[Invoice Date & Time], "dd-mm-yyyy" ), rename the column header Date Only and press Enter.

4. Select the Flattened PivotTable option in the PivotTable command

5. You have options to select the New Worksheet or Existing worksheet and select the worksheet location.  Once you press the OK button, Excel adds an empty pivot table in the worksheet. 

6. You drag the fields Invoice No, Counter No, and newly added column Date Only to the Rows Area.

Remove Time from a Date in Excel with Power Query

Excel Power Query is an ETL data transformation tool that enables you to clean and shape data from multiple sources. You can access, query, and combine data from multiple sources, including databases, tables, and text files. Additionally, Power Query can also be used for creating reports, visualizations, and deriving insights from data.

Steps to convert text to numbers using Power Query

1. Select the range of cells A1:D16 in the Data Sheet
2. Choose From Table/Range option in Data Tab – to open the Power Query window.
3. Rename the Query name PQData

4. Go to Transform Tab, Select the Date Only option in the Date Command.

5. Select the Close & Load command and Press Close & Load To… – to popup Import Data dialog box.

6. In the Import Data dialog box, select the Table option, and provide the location in the  Existing worksheet to insert the transformed Power Query table into your worksheet.

Remove Time from a Date in Excel with VBA

VBA is a programming language in excel. It can be used to record repetitive tasks, increase user productivity, and reduce errors. It can also be used to create custom functions and formulas, automate processes, and create custom user interfaces.

Sub RemoveTime()

'Declare variables
Dim rge As Range
Dim yy As Integer
Dim mm As Integer
Dim dd As Integer

'Assign values
Set rge = Sheets("VBA ").Range("B2:B16")

'loop through each cell in the range
For Each rg In rge

    'Assign Year, Month and Day to the respective variables
    yy = Year(rg.Value)
    mm = Month(rg.Value)
    dd = Day(rg.Value)
    
    'Set the date in column E
    rg.Offset(0, 3).Value = DateSerial(yy, mm, dd)
  
Next

End Sub

Add the above code to your VBE module.

Macro declares the variables rge, yy, mm, and dd. Assign the range of cells to the variable rge. Loop through the rge, an array of cells. The Year(), Month() and Day() functions return the year, month and day, respectively, of the given date-time serial number and assigned to the respective variables yy, mm, and dd. DateSerial() function returns the decimal number of the date and store it in the respective cell in column E

To run the RemoveTime macro. Press Alt + F8, the keyboard shortcut to open the Macro dialog box, Choose the RemoveTime Macro and then Press Run.

Remove Time from a Date in Excel with Office Scripts

Office script allows you to record the repetitive task, automate, edit and run. It works in both desktop and web excel versions. You can share the office script with your team.  These scripts can be triggered by events, such as a user clicking a button.

function main(workbook: ExcelScript.Workbook) {

    // Assign the range of cells to the variable
    let ws = workbook.getWorksheet("Office Scripts");
    let rng = ws.getRange("B2:B16");
    let rows = rng.getRowCount();

    // loop through the selected range of cells
    for (let i = 0; i < rows; i++) {
      
        // get current cell value and store it in the variable cellValue
        let cellValue = rng.getCell(i, 0).getValue() as number
        
        // remove time and assign only date 
        let noTime = Math.floor(cellValue);

        // set the value to column E
        rng.getCell(i, 3).setValue(noTime)
    }
}

Script assigns the range of cells B2:B16 to the variable rng. Next, loop through each cell in the given range rng, assign the current cell value to the variable cellValue, remove time from the cellValue using the Math.floor() function and set the value to the respective cell in column E.

Follow the below steps to execute the Remove Time Office script in your excel.

1. Go to Automate menu.
2. Select the Remove Time Script – to open the Code editor on the right side of your excel application.
3. Press Run 

Conclusions

Most often you need to work with the Date and Time function in your analysis. Date and Time information can give background to the data and allow for a more detailed analysis.

For example, when you are working with sales data, the date and time can be used to compare sales trends over time or to identify seasonal trends. You may have to report YOY, YTD, MTD, MOM, etc., using the Date functions.

You learned how to remove time from a date using excel Shortcut, Find and Replace, and Number Format. Also, in detail how the Excel functions DATE, DATEVALUE, INT and TEXT are worked to delete the time from a date.

You can use the Power Pivot and Power Query methods when working with multiple data sources. VBA or Offices Scripts code helps you to remove time from a date in your dashboard or report automation.

Have you tried to Remove Time from a Date in Excel? Did you know any other method to do this? Let me know in the comments below!