How to create a calculated date column for recurring dates?

gable

New Member
Joined
Jun 23, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Web
I have created a table which calculates a date using two fields.
The three fields are
  1. Start date (Date field)
  2. Recurrence (Choice: Month, Year, 3 years)
  3. Next recurrence (Calculated column with date result)
The rule is If Recurrence is Month the Next recurrence will be Start date - 1 day and + 1 month (add 1 month after 06.17 23:59:59)
So 2022.06.17 will be 2022.07.16 and after it it will be 2022.08.16 ...
The same applies as years (add 1 year after 06.17 23:59:59)
2022.06.17 will be 2023.06.16 and after it it will be 2024.06.16 ...
and 3 years (add 3 years after 06.17 23:59:59)
2022.06.17 will be 2025.06.16 and after it it will be 2028.06.16 ...
I have wrote this formula for the calculated column:
=IF([Recurrence]="Month";
DATE(YEAR(TODAY());MONTH(TODAY());DAY([Start date])-1)+TIME(23;59;59);
IF([Recurrence]="Year";
DATE(YEAR(TODAY())+1;MONTH([Start date]);DAY([Start date])-1)+TIME(23;59;59);
DATE(YEAR([Stat date])+3;MONTH([Start date]);DAY([Start date])-1+TIME(23;59;59))))

But the problem is if the start date is 2022.06.17 and today is 2022.06.20 the today formula will return this month (06) and subtracts 1 day so the date will be 2022.06.17 but it should be 2022.07.17
Can someone help me to solve this with me?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Welcome to the Forum!

Your rule says nothing about today's date. So it's not clear why your formula is using TODAY()?

Also, if your Start Date is, say 31 January 2022, and the increment is one month, what recurring dates do you want to show for Feb, March and April?
 
Upvote 0
Welcome to the Forum!

Your rule says nothing about today's date. So it's not clear why your formula is using TODAY()?

Also, if your Start Date is, say 31 January 2022, and the increment is one month, what recurring dates do you want to show for Feb, March and April?
Thank you for your reply,

The Today if for always showing the current month, but its wrong. It needs to be showing the next "billing" period.

The rule is basically to add the [Recurrence] and subtract -1 days from the [Start date]
If the date passed show the next recurrence.
 
Upvote 0
I have made adjustments for my formula

Excel Formula:
=IF([@Recurrence]="Month";
DATE(YEAR([@[Start date]]);MONTH([@[Start date]])+1;DAY([@[Start date]])-1);
IF([@Recurrence]="Year";
DATE(YEAR([@[Start date]])+1;MONTH([@[Start date]]);DAY([@[Start date]])-1);
IF([@Recurrence]="3 Years";
DATE(YEAR([@[Start date]])+3;MONTH([@[Start date]]);DAY([@[Start date]])-1))))

Recurrence​
Start date​
Next billing cycle​
TODAY​
Next billing cycle should be (Manual text)​
Month​
2022.01.10​
2022.02.09​
2022.06.24​
2022.07.09​
Year​
2022.01.10​
2023.01.09​
2022.06.24​
2023.01.09​
3 Years​
2022.01.10​
2025.01.09​
2022.06.24​
2025.01.09​
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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