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

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.

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

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

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?

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)

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