How To Get Rid Of Date Format In Excel

There are a couple of ways you tin remove the year from a engagement shown in Microsoft Excel. Yous can use custom jail cell formatting to hide it or utilize diverse functions like CONCATENATE to remove it completely. Here'due south how.

Custom Number Formatting

While a date value in an Excel cell is technically a number, Microsoft Excel uses a particular type of formatting to display information technology. The same is truthful for currency values, where currency symbols are added to values to show that they relate to money. When you lot add together a date in Excel in a format that information technology recognizes, Excel automatically changes the jail cell number format to the "Appointment" blazon.

You can customize this type to display the date value in different means. For example, you can switch between "11/02/2021" (in the DD/MM/YYYY format unremarkably used in the U.K.) and "2021-02-xi" for 11 Feb 2021.

You lot tin customize this number format, nevertheless, to remove the year entirely from view. The value itself won't change, merely Excel won't show the year value thanks to custom number formatting.

To do this, highlight the cells containing your original date values, then select the "Dwelling house" tab on the ribbon bar. From there, select the arrow side by side to the number format driblet-downwards card and cull the "More Number Formats" pick.

In the "Format Cells" carte du jour, select a style of formatting that you similar from the "Date" options provided. Once that'south selected, choose the "Custom" selection.

In the "Custom" selection, you'll encounter the date number formatting manner shown as text in the "Type" box. For instance, "11/02/2021" would show as "dd/mm/yyyy" in this box.

To remove the year, brand sure to remove any references to "yyyy" or "yy" in the "Type" box as well as any surplus delimiters, such as a dash or forward slash. For case, "11/02/2021" would crave you to remove "/yyyy" from the "dd/mm/yyyy" type box, leaving "dd/mm" in identify.

In one case you lot've made your changes, select the "OK" push button.

Changing the jail cell number type will hide the twelvemonth, just it won't remove it. If you want to restore the yr, change your cell number blazon to a date blazon that includes the year again.

CONCATENATE Office

Concatenation is a term that basically means to link or combine ii or more pieces of information. In Microsoft Excel terms, concatenation tin can be used to add various text strings, numbers, or cell values together in a new cell.

If you want to remove the twelvemonth from a appointment in Excel, you lot could combine the output from two functions (such as DAY or Month) together using CONCATENATE. Rather than hiding the year from view using custom number formatting, CONCATENATE (with DAY and MONTH) allows you to create a separate value that doesn't include the year at all.

To do this, open an Excel workbook containing your date values, or create a new workbook and place date values in separate cells. To use CONCATENATE with DAY and MONTH, insert a new function using this structure, replacing the cell reference (A2) with a reference to the cell containing your date:

=CONCATENATE(Mean solar day(A2),"/",Calendar month(A2))

The values returned past Solar day and MONTH are separated past a special delimiter character, such equally a comma or slash, to show that the returned value is a date. Each value (DAY, the delimiter, and MONTH) are separated by a comma in the CONCATENATE part.

In this example, prison cell A2 contains a date value (11/12/2021) in the DD/MM/YYYY format. Using CONCATENATE with Day and Month, the day (11) and month (12) from the appointment value in A2 are placed in another cell separated by the delimiter, which, in this case, is a forward slash.

You can customize this formula to change the order of the day and month values (eg. MM/DD) or to employ a different delimiter graphic symbol. You can also use the fill handle to copy this formula into other cells in your column to remove the year from multiple date values.

RELATED: How to Automatically Fill up Sequential Information into Excel with the Fill up Handle

Source: https://www.howtogeek.com/714809/how-to-remove-a-year-from-a-date-in-microsoft-excel/

Posted by: mcclearylonswellot.blogspot.com

Share this post