Hello again Exellers, today let’s look at 3 ways to remove the Days portion from a date in Excel. This was an issue one of my collgeaues came across this week. Let’s take a look at the story.

He was trying to analyse his data set by Monthly Sales using a Pivot Table. You can see a sample of the data below.

The Pivot analysis looks like this; it analysed data the Day and Month together as expected. He just wants Monthly Sales total though.

So, my colleague duly selected all of his data and then went into

• Home Tab
• Number Group
• Custom Number From the drop down box
• More Number Formats
• Selection of formatting was mm/yyyy to reformat the data without the Day

The data was refreshed, but the result was the same. Even though the data looked like it only contained Month and Year, the Day part of the date is still in the cell, which you can see if you click into an individual cell. It looks like the cell contains Jan -2016 but it still contains the Day.

So to get the correctly summarised Sales Volumes Per Month, we need to remove the Day section of the Date.

There are a few ways to remove the Day from a Date. Let’s take a look at how to do it –3 ways.

Use the TEXT Function

This function will convert numeric values into text. The Syntax of TEXT is

=TEXT(value, format_text)

Where

value – this is required and is the numeric value you converted into text

format_text – this is the text strong that defines the formatting of the text

So, let’s give this method a go. First we can insert a helper column into our data – and type the following formula

=TEXT(D2,”mmm-yyyy”)

This takes the value in D2 and formats it with mmm-yyyy. All that’s needed now is to double click or drag the fill handle to populate the rest of the cells in Column F with the formula.

Refresh the Pivot Table by taking in the new helper column and use Date_2 in the Row Area.

Awesome. Job Done!.

Use the DAY Function

An alternative way is to use the DAY Function, the syntax of DAY is

=DAY(serial_number)

Where

serial_number – this is required and is the date of the day that you are trying to find.

So, let’s give this method a go. First we can insert another helper column into our data (Date-2) – and type the following formula

=D2-DAY(D2)+1

Let’s try the Pivot Table again after it has been refreshed to take in the new helper column.

Yes, that works too as a solution.

Use the CONCATENATE Function

The third and final way we we look at today is using the CONCATENATE Function. If you want to read more about CONCATENATE then you can read my blog posts here.

The syntax of this formula is

=CONCATENATE(TEXT1,TEXT2…)

Up to 255 text entries can be added to the function and each one of them should be separated by a comma.

What we ca do is use CONCANTENATE with MONTH and YEAR, both of which return the MONTH and YEAR from a number respectively

The formula looks like this –

We are joining the values of MONTH and YEAR from our orignal Date Column, seperated by a dash in between.

Let’s try the Pivot Table again after it has been refreshed to take in the new helper column.

Excellent, that works too.

Which method would you use to carry out this data tidy up?.

If you want to download the example workbook for this blog post you can do so here.

[wpdm_package id=’10074′]

Dont forget to sign up to the Excel at Excel Newletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.

• Create A Micro Chart In Excel
• How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel
• Create A Timestamp In Excel With Formulas
• Use TRIM for a speedy data clean up