Excel Formula Help

[happy_2008]

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

 

[kinobestew123]

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.

 

[happy_2008]

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

 

[AHarvey]

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

 

[happy_2008]

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?

 

[kinobestew123]

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)

 

[wrightyrx7]

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