Formula to show future date

rsharma017

New Member
Joined
Aug 7, 2017
Messages
7
I have a set of transactions that are showing an aging bucket 0-30, 31-60, 61-90, 91+ based on the date entered on the spreadsheet. I would like to know if possible to show the date a transaction will roll/move to the next aging bucket.

A1 Date in 05/03/2021
B1 Aging Bucket : Has a formula that shows based on date what the aging bucket mentioned is. As of today 06/03/2021 its in 31-60 Days Bucket.
C1 - This is where i need help to show date when B1 will move to 61-90 Days bucket

This would be for any type ; for example the date in C1 to show when B1 will move from 0-30 to 31-60, 31-60 to 61-90, 61-90 to 91+

Any help will be appreciated.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,987
Office Version
  1. 2016
Platform
  1. Windows
Hi RSharma017,

You don't say what you want if it's already in the last bucket so I've displayed that text.

RSharma017.xlsx
ABC
1DateAging BucketDate into next bucket
25/3/202131-607/3/2021
36/3/20210-307/4/2021
41/1/202191+In Last Bucket
52/3/202191+In Last Bucket
63/8/202161-906/7/2021
74/10/202131-606/10/2021
85/13/20210-306/13/2021
93/5/202191+In Last Bucket
102/2/202191+In Last Bucket
113/7/202161-906/6/2021
124/9/202131-606/9/2021
136/4/20210-307/5/2021
143/3/202191+In Last Bucket
154/5/202131-606/5/2021
165/8/20210-306/8/2021
Sheet1
Cell Formulas
RangeFormula
B2:B16B2=CHOOSE(MAX(1,MIN(INT((TODAY()-A2-1)/30)+1,4)),"0-30","31-60","61-90","91+")
C2:C16C2=CHOOSE(MAX(1,MIN(INT((TODAY()-A2-1)/30)+1,4)),A2+31,A2+61,A2+91,"In Last Bucket")
 
Solution

rsharma017

New Member
Joined
Aug 7, 2017
Messages
7
Hi RSharma017,

You don't say what you want if it's already in the last bucket so I've displayed that text.

RSharma017.xlsx
ABC
1DateAging BucketDate into next bucket
25/3/202131-607/3/2021
36/3/20210-307/4/2021
41/1/202191+In Last Bucket
52/3/202191+In Last Bucket
63/8/202161-906/7/2021
74/10/202131-606/10/2021
85/13/20210-306/13/2021
93/5/202191+In Last Bucket
102/2/202191+In Last Bucket
113/7/202161-906/6/2021
124/9/202131-606/9/2021
136/4/20210-307/5/2021
143/3/202191+In Last Bucket
154/5/202131-606/5/2021
165/8/20210-306/8/2021
Sheet1
Cell Formulas
RangeFormula
B2:B16B2=CHOOSE(MAX(1,MIN(INT((TODAY()-A2-1)/30)+1,4)),"0-30","31-60","61-90","91+")
C2:C16C2=CHOOSE(MAX(1,MIN(INT((TODAY()-A2-1)/30)+1,4)),A2+31,A2+61,A2+91,"In Last Bucket")
Thank you so much. This worked perfectly.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,987
Office Version
  1. 2016
Platform
  1. Windows
You're welcome!
Thanks for the feedback.
 

James Clear

New Member
Joined
Jul 12, 2021
Messages
19
Office Version
  1. 2016
  2. 2013
  3. 2010
Platform
  1. Windows

ADVERTISEMENT

Hi RSharma017,

You don't say what you want if it's already in the last bucket so I've displayed that text.

RSharma017.xlsx
ABC
1DateAging BucketDate into next bucket
25/3/202131-607/3/2021
36/3/20210-307/4/2021
41/1/202191+In Last Bucket
52/3/202191+In Last Bucket
63/8/202161-906/7/2021
74/10/202131-606/10/2021
85/13/20210-306/13/2021
93/5/202191+In Last Bucket
102/2/202191+In Last Bucket
113/7/202161-906/6/2021
124/9/202131-606/9/2021
136/4/20210-307/5/2021
143/3/202191+In Last Bucket
154/5/202131-606/5/2021
165/8/20210-306/8/2021
Sheet1
Cell Formulas
RangeFormula
B2:B16B2=CHOOSE(MAX(1,MIN(INT((TODAY()-A2-1)/30)+1,4)),"0-30","31-60","61-90","91+")
C2:C16C2=CHOOSE(MAX(1,MIN(INT((TODAY()-A2-1)/30)+1,4)),A2+31,A2+61,A2+91,"In Last Bucket")
Hi Toadstool,

Can you please help me with this... Your formula is very deep when it comes to an understanding.

I would like to know, what if we would like to count national or state holidays in-between and exclude Sunday as well.

How can we tweak this formula and make it useful by using the same functions. pls guide.

Thanks
James
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,987
Office Version
  1. 2016
Platform
  1. Windows
James,

First let me explain the formula
Excel Formula:
=CHOOSE(MAX(1,MIN(INT((TODAY()-A2-1)/30)+1,4)),"0-30","31-60","61-90","91+")

CHOOSE takes a value and let's you choose a different action for each integer result. We have 4 options ("0-30", "31-60", "61-90" or "91+") so the value must be 1, 2, 3 or 4.

The MIN(INT((TODAY()-A2-1)/30)+1,4) takes the number of days difference, divided by 30 but limits it to a maximum of 4, so if the date difference is 365 days then instead of 13 it limits to 4, the last option.

The MAX(1,calc) does the same for date difference which are negative or zero, it forces a 1 for the first option.

So if the date difference between TODAY() and A2 is 79 days then the value calculates as 3 so it returns the text "61-90".


To exclude Sundays and Holidays we'll need to use two new functions, NETWORKDAYS.INTL (calculate working days between two dates) and WORKDAY.INTL (calculate working day from start date and a number of days) which both have an option 11 to specify Sunday as the non-working day and let you supply a list of holiday dates.
NOTE: If I subtract yesterday from today I get 1 but if I specify yesterday and today using NETWORKDAYS.INTL it returns 2 because it's counting working days, not days difference, so our formula will need to subtract an extra day.

Here is an example using holidays for the State of Hawaii (which could be on a different sheet):

JamesClear-1.xlsx
ABCDEFG
1DateAging BucketDate into next bucketHolidayDate
26/1/202131-608/13/2021New Year's Day1 Jan 2021
35/31/202131-608/12/2021Martin Luther King Jr. Day18 Jan 2021
41/1/202191+In Last BucketWashington's Birthday15 Feb 2021
52/3/202191+In Last BucketPrince Kūhiō Day26 Mar 2021
63/8/202191+In Last BucketGood Friday2 Apr 2021
74/10/202191+In Last BucketMemorial Day31 May 2021
85/13/202161-909/1/2021King Kamehameha I Day11 Jun 2021
93/5/202191+In Last BucketIndependence Day5 Jul 2021
102/2/202191+In Last BucketHawaii Admission Day20 Aug 2021
113/7/202191+In Last BucketLabor Day6 Sept 2021
124/9/202191+In Last BucketElection Day (US)2 Nov 2021
136/4/202131-608/17/2021Veterans Day11 Nov 2021
143/3/202191+In Last BucketThanksgiving25 Nov 2021
154/5/202191+In Last BucketChristmas Day24 Dec 2021
165/8/202161-908/27/2021New Year's Day31 Dec 2021
177/31/20210-309/8/2021
188/1/20210-309/8/2021
19
Sheet2
Cell Formulas
RangeFormula
B2:B18B2=CHOOSE(MAX(1,MIN(INT((NETWORKDAYS.INTL(A2,TODAY(),11,Sheet2!$G$2:$G$99)-2)/30)+1,4)),"0-30","31-60","61-90","91+")
C2:C18C2=CHOOSE(MAX(1,MIN(INT((NETWORKDAYS.INTL(A2,TODAY(),11,Sheet2!$G$2:$G$99)-2)/30)+1,4)),WORKDAY.INTL(A2,31,11,Sheet2!$G$2:$G$99),WORKDAY.INTL(A2,61,11,Sheet2!$G$2:$G$99),WORKDAY.INTL(A2,91,11,Sheet2!$G$2:$G$99),"In Last Bucket")
 

James Clear

New Member
Joined
Jul 12, 2021
Messages
19
Office Version
  1. 2016
  2. 2013
  3. 2010
Platform
  1. Windows
James,

First let me explain the formula
Excel Formula:
=CHOOSE(MAX(1,MIN(INT((TODAY()-A2-1)/30)+1,4)),"0-30","31-60","61-90","91+")

CHOOSE takes a value and let's you choose a different action for each integer result. We have 4 options ("0-30", "31-60", "61-90" or "91+") so the value must be 1, 2, 3 or 4.

The MIN(INT((TODAY()-A2-1)/30)+1,4) takes the number of days difference, divided by 30 but limits it to a maximum of 4, so if the date difference is 365 days then instead of 13 it limits to 4, the last option.

The MAX(1,calc) does the same for date difference which are negative or zero, it forces a 1 for the first option.

So if the date difference between TODAY() and A2 is 79 days then the value calculates as 3 so it returns the text "61-90".


To exclude Sundays and Holidays we'll need to use two new functions, NETWORKDAYS.INTL (calculate working days between two dates) and WORKDAY.INTL (calculate working day from start date and a number of days) which both have an option 11 to specify Sunday as the non-working day and let you supply a list of holiday dates.
NOTE: If I subtract yesterday from today I get 1 but if I specify yesterday and today using NETWORKDAYS.INTL it returns 2 because it's counting working days, not days difference, so our formula will need to subtract an extra day.

Here is an example using holidays for the State of Hawaii (which could be on a different sheet):

JamesClear-1.xlsx
ABCDEFG
1DateAging BucketDate into next bucketHolidayDate
26/1/202131-608/13/2021New Year's Day1 Jan 2021
35/31/202131-608/12/2021Martin Luther King Jr. Day18 Jan 2021
41/1/202191+In Last BucketWashington's Birthday15 Feb 2021
52/3/202191+In Last BucketPrince Kūhiō Day26 Mar 2021
63/8/202191+In Last BucketGood Friday2 Apr 2021
74/10/202191+In Last BucketMemorial Day31 May 2021
85/13/202161-909/1/2021King Kamehameha I Day11 Jun 2021
93/5/202191+In Last BucketIndependence Day5 Jul 2021
102/2/202191+In Last BucketHawaii Admission Day20 Aug 2021
113/7/202191+In Last BucketLabor Day6 Sept 2021
124/9/202191+In Last BucketElection Day (US)2 Nov 2021
136/4/202131-608/17/2021Veterans Day11 Nov 2021
143/3/202191+In Last BucketThanksgiving25 Nov 2021
154/5/202191+In Last BucketChristmas Day24 Dec 2021
165/8/202161-908/27/2021New Year's Day31 Dec 2021
177/31/20210-309/8/2021
188/1/20210-309/8/2021
19
Sheet2
Cell Formulas
RangeFormula
B2:B18B2=CHOOSE(MAX(1,MIN(INT((NETWORKDAYS.INTL(A2,TODAY(),11,Sheet2!$G$2:$G$99)-2)/30)+1,4)),"0-30","31-60","61-90","91+")
C2:C18C2=CHOOSE(MAX(1,MIN(INT((NETWORKDAYS.INTL(A2,TODAY(),11,Sheet2!$G$2:$G$99)-2)/30)+1,4)),WORKDAY.INTL(A2,31,11,Sheet2!$G$2:$G$99),WORKDAY.INTL(A2,61,11,Sheet2!$G$2:$G$99),WORKDAY.INTL(A2,91,11,Sheet2!$G$2:$G$99),"In Last Bucket")

Thank you so much for your great explanation and valuable time.
 

Forum statistics

Threads
1,143,674
Messages
5,720,234
Members
422,271
Latest member
xbaca

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top