Excel Formula Help

Soldato
Joined
27 Aug 2005
Posts
3,610
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 :)
 
Associate
Joined
6 Feb 2008
Posts
1,750
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.
 
Soldato
OP
Joined
27 Aug 2005
Posts
3,610
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?
 
Associate
Joined
6 Feb 2008
Posts
1,750
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)
 
Back
Top Bottom