none
Converting dd-mm-yyyy to mm-yyyy RRS feed

  • 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

Answers

  • 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)))))

    Copied from: http://www.askeygeek.com/excel-formula-to-convert-ddmmyyyy-to-mmddyyyy/

    Thursday, July 20, 2017 7:10 PM
  • =DATE(MID(A2,7,4),MID(A2,4,2),MID(A2,1,2))

    dd/mm/yyyy to mm/dd/yyyy

    Tuesday, December 26, 2017 7:19 PM