# Converting dd-mm-yyyy to mm-yyyy

• ### Question

• I have a data set that contains dates in dd-mm-yyyy format. I would like to convert these dates into mm-yyyy dates so I can sum value associated with different dates in a month to and aggregate them into monthly sub-totals.

Everytime I use the format() function to perform this conversion the original date (i.e. dd-mm-yyyy) is preserved for sorting/manipulation purposes and I am not able to sum daily totals into monthly totals.

I would like to convert the dd-mm-yyyy date to an absolute date, for example:

from 09-Nov-2011 to 01-Nov-2011 (or the appropriate date)

from 08-Nov-2011 to 01-Nov-2011

and so forth?

Saturday, November 26, 2011 6:13 AM

• hi Mark,

a possibility would be to adding a new column and use the following formula
= DATE (YEAR (A1), MONTH (A1), 1)
--
isabelle

Saturday, November 26, 2011 6:51 AM
• or to do sum column B cells if column A is the month of February,

=SUMPRODUCT(--(MONTH(A2:A100)=2)*(B2:B100))

--
isabelle

Saturday, November 26, 2011 7:12 AM
• The End of Month function can be used to return the first day of a month. If you are using xl version prior to xl2007 then you may need to load the Analysis ToolPak. To do this select Menu item Tools -> Add-ins and check the box against Analysis ToolPak.

Assume that the date is in Cell A2

=EOMONTH(A2,0)      Returns the end of the month of date in cell A2

=EOMONTH(A2,-1)     Returns the end of the previous month.

=EOMONTH(A2,-1)+1    Returns end of previous month plus 1 day which is 1st day of month.

Regards, OssieMac
Saturday, November 26, 2011 10:40 AM
• Another option may be to use another column with a formula like:

=text(a1,"yyyy-mm")

This won't be a date, but will look like:
2011-11
2008-03
...

You should be able to sort by this column in nice order (year first is very
nice!).

And if you're using Data|subtotals (in xl2003 menus), you may want to look at
using pivottables.  They're powerful and after an hour, pretty easy to work
with!

Mark, Bristoll wrote:
>
> I have a data set that contains dates in dd-mm-yyyy format. I would like to
> convert these dates into mm-yyyy dates so I can sum value associated with
> different dates in a month to and aggregate them into monthly sub-totals.
>
> Everytime I use the format() function to perform this conversion the original
> date (i.e. dd-mm-yyyy) is preserved for sorting/manipulation purposes and I am
> not able to sum daily totals into monthly totals.
>
> I would like to convert the dd-mm-yyyy date to an absolute date, for example:
>
> from 09-Nov-2011 to 01-Nov-2011 (or the appropriate date)
>
> from 08-Nov-2011 to 01-Nov-2011
>
> and so forth?

--

Dave Peterson

Saturday, November 26, 2011 1:58 PM

### All replies

• hi Mark,

a possibility would be to adding a new column and use the following formula
= DATE (YEAR (A1), MONTH (A1), 1)
--
isabelle

Saturday, November 26, 2011 6:51 AM
• or to do sum column B cells if column A is the month of February,

=SUMPRODUCT(--(MONTH(A2:A100)=2)*(B2:B100))

--
isabelle

Saturday, November 26, 2011 7:12 AM
• The End of Month function can be used to return the first day of a month. If you are using xl version prior to xl2007 then you may need to load the Analysis ToolPak. To do this select Menu item Tools -> Add-ins and check the box against Analysis ToolPak.

Assume that the date is in Cell A2

=EOMONTH(A2,0)      Returns the end of the month of date in cell A2

=EOMONTH(A2,-1)     Returns the end of the previous month.

=EOMONTH(A2,-1)+1    Returns end of previous month plus 1 day which is 1st day of month.

Regards, OssieMac
Saturday, November 26, 2011 10:40 AM
• Another option may be to use another column with a formula like:

=text(a1,"yyyy-mm")

This won't be a date, but will look like:
2011-11
2008-03
...

You should be able to sort by this column in nice order (year first is very
nice!).

And if you're using Data|subtotals (in xl2003 menus), you may want to look at
using pivottables.  They're powerful and after an hour, pretty easy to work
with!

Mark, Bristoll wrote:
>
> I have a data set that contains dates in dd-mm-yyyy format. I would like to
> convert these dates into mm-yyyy dates so I can sum value associated with
> different dates in a month to and aggregate them into monthly sub-totals.
>
> Everytime I use the format() function to perform this conversion the original
> date (i.e. dd-mm-yyyy) is preserved for sorting/manipulation purposes and I am
> not able to sum daily totals into monthly totals.
>
> I would like to convert the dd-mm-yyyy date to an absolute date, for example:
>
> from 09-Nov-2011 to 01-Nov-2011 (or the appropriate date)
>
> from 08-Nov-2011 to 01-Nov-2011
>
> and so forth?

--

Dave Peterson

Saturday, November 26, 2011 1:58 PM
• You may try the below formula:

=IF(B1=””,””,IF(DAY(B1)>12,DAY(B1)&”/”&MONTH(B1)&”/”&YEAR(B1),VALUE(DATE(YEAR(B1),

DAY(B1),MONTH(B1)))))