1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Excel Formula Help

Discussion in 'HTML, Graphics & Programming' started by happy_2008, Nov 28, 2018.

  1. happy_2008

    Mobster

    Joined: Aug 27, 2005

    Posts: 2,788

    Hi All, can someone help me with a excel formula? I have a spreadsheet that lists some data by maturity date and I need to list the days into certain buckets based on a certain point of the month. I’ll try to list the example below:


    Reporting date is 31/10/2018

    The data has a maturity date of 19/11/2018.

    So number of days is 19

    I want the excel formula to split the data into the actual day buckets:

    7 days

    2weeks

    3weeks

    4weeks

    5week

    6weeks

    7weeks

    Etc
    Any advice would be greatly appreciated :)
     
  2. kinobestew123

    Wise Guy

    Joined: Feb 6, 2008

    Posts: 1,515

    Assuming reporting date is in A1 and maturity date is in B1, can you not just have a column that does =ROUNDUP((B1-A1)/7,0)

    That will then give you a number 1, 2, 3, 4 which correspond to your buckets. 1 being <=7 days, 2 being 8-14 days and so on.

    You can then pivot or filter based on this new column to give you whatever you like.
     
  3. happy_2008

    Mobster

    Joined: Aug 27, 2005

    Posts: 2,788

    That did work but my last two buckets are 3m and >=3m which I assume the formula above cannot calculate?
     
  4. AHarvey

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 9,062

    Location: Stoke area

    So that's greater than or equal 3 months and just 3 months?

    You could have an IF that if says if ROUNDUP((B1-A1)/7,0) enter >=3m in the cell
     
  5. happy_2008

    Mobster

    Joined: Aug 27, 2005

    Posts: 2,788

    The last two buckets are:
    3m - between 8 weeks and 3months
    >3m - greater than 3 months.

    Will the above formula work for this? If so how would it be written?
     
  6. kinobestew123

    Wise Guy

    Joined: Feb 6, 2008

    Posts: 1,515

    Is it accurate enough to assume 12 weeks is 3 months? If so:

    =IF(ROUNDUP((B1-A1)/7,0)<8,ROUNDUP((B1-A1)/7,0),IF(ROUNDUP((B1-A1)/7,0)<12,"3m",">3m"))

    Note, I recommend testing with some dates to check that they're falling in to the buckets you expect. If not, tweak the <9 and <12 to what you need them to be (i.e. maybe <8 and <13)
     
  7. wrightyrx7

    Associate

    Joined: Sep 18, 2013

    Posts: 1

    Could try

    =IF(ROUNDUP((DATEDIF(A1,B1,"d")/7),0)<8,(ROUNDUP((DATEDIF(A1,B1,"d")/7),0)&"Weeks"),IF((DATEDIF(A1,B1,"M"))<3,"3 Months",">=3 Months"))