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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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")
 
Upvote 0
Solution
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.
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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
Back
Top