0

How can I change a date in an Excel file to eliminate the day and be left with only the month and year?  E.g., convert 4/4/2019 to April-2019?

I am aware that I can change the format, but if I do so, the date in the background will still be 4/4/2019 and this is not what I want. I would like to completely remove the day and only be left with the month and year. I would like to be able to sort the dates chronologically so if this isn't possible I would be happy to have the first of the months displayed instead.

I am working with thousands of records (transactions) and would like to perform analysis based on the months, not individual days. Also, this is to enable me to create pivot tables and have just the months displayed.

G-Man Says 'Reinstate Monica'
  • 8,029
sonic99
  • 51

2 Answers2

2

In a Pivot Table you can group a date field by months and years, then remove the from the table so you end up looking at just years and months. This would leave the original data as it is, using real dates.

If you use a helper column with a clever formula to show eg "2019 April" as a piece of text then in your Pivot Table you would not be able to show things like subtotals for years unless you manually group the 12 months together for each year. Also sorting will be annoying because you will have the months sorted alphabetically! The only way for Excel to sort "Chronologically" is if the underlying data is a real date.

So I would suggest changing the format is your simplest option, then group in Pivot Tables. But if you want to be able to sort the original data by month, and then by something else (eg a category, or sales value or whatever), and the different days of the month make this sort incorrectly, then in a helper column use something like this to set everything to first of the month:

=Date(Year(A1),Month(A1),1)

You can then use this column directly for sorting (best plan if you will add more data rows in future). Alternatively, copy and paste special > values over the original dates.

Again, change the format to "mmm YY" or whatever suits you best for presentation purposes.

AdamV
  • 6,396
2

Strictly speaking, what you’re asking for is impossible.  Excel doesn’t store months and years; it stores instants in time.  Even if you enter “7/15/2019”, Excel stores it as 7/15/2019  12:00:00 AM.

You go on to say that you would like to be able to sort the dates chronologically.  I’m not sure what you mean.  If you want to be able to sort rows by date such that 4/4 comes before 4/15 which comes before 4/17, then you should just change the format to suppress the display of the day of the month.  But, if you want to be able to sort rows by date such that 4/4, 4/15 and 4/17 are considered to be equivalent, and the tie is broken by some other sort field, then (as you suggest) you should convert all the dates in the same month to be the same date; e.g., 4/1 (April 1).

Or maybe 4/30 (April 30).  As long as you have the cell formatted to display only the month and year, it probably doesn’t matter much what the day is.

Either way, you can use the EOMONTH (end of month) function.  (I’ll assume that your exact dates are in Column A, starting in Cell A1.)  If you’re willing to map all dates in April 2019 to 4/30/2019, then use

=EOMONTH(A1,0)

which reports the last day of the month that A1 is in.  If you prefer 4/1/2019, then use

=EOMONTH(A1,-1)+1

where EOMONTH(A1,-1) returns the last day of the previous month (3/31/2019) and then the +1 gives you 4/1/2019.

G-Man Says 'Reinstate Monica'
  • 8,029