Automatic cell update
I have a payment date column “A” (the specific day of each month specific payments are made) and another column “B” that denotes if the payment is fixed or variable payment. (fixed reoccurs on the same day each month)
For the payments identified as fixed in column “B” i would like column “A” to automatically update to the next months payment date when the current payment date is in the past.
How can i achieve this is it a formula or an automated work? any help with this would be amazing
Answers
-
This is a complicated answer but maybe it will help point you in a direction.
Using the sheet below, yellow highlighted items are inputs, the remainder are calculations. Payment Date is the end result.
If a Fixed Payment, enter the date in Fixed Payment Date. If Variable, Enter Date in Variable Payment Date.
It may be possible to enter all in one field and use the Type Column to get to the Payment Due, but it's easier to explain this way.
These are formulas
Payment Date:
=IF(Type@row = "Fixed", [Fixed Payment Date Helper]@row, [Variable Payment Date]@row)
Variable Payment Date: No formula, input value
Fixed Payment Date: No formula, input value
Type:
=IF(ISBLANK([Variable Payment Date]@row), "Fixed", "Variable")
Fixed Payment Date Helper: Note that I hard coded 2024. I'll let you figure out how to get it from a formula.
=IF([Curr Date Helper]@row > [Fixed Payment Date]@row, [Next Payment Date]@row, [Fixed Payment Date]@row + "/2024")
CurrDate:
=TODAY()
Current Date Helper:
=LEFT(CurrDate@row, 2) + "/" + MID(CurrDate@row, 4, 2)
Year: Hard coded to 24. You can do a formula. Challenge is when year rolls over.
Fixed Month:
=LEFT([Fixed Payment Date]@row, 2)
Fixed Day:
=MID([Fixed Payment Date]@row, 4, 2)
Payment Date Month:
=VALUE(LEFT([Fixed Payment Date]@row, 2))
Next Payment Month:
=IF([Payment Date Month]@row + 1 = 13, 1, [Payment Date Month]@row + 1)
Next Payment Date:
=IF([Next Payment Month]@row < 10, "0" + [Next Payment Month]@row + "/" + [Fixed Day]@row + "/" + Year@row, [Next Payment Month]@row + "/" + [Fixed Day]@row + "/" + Year@row)
Best of luck.
-
Thanks seems very complicated for what seems to be a simple recurring type function thanks i will give it a crack:-)
-
@dojones Thanks it works great however i use day / month / year format and when i try to adjust the formulas i keep breaking them any chance you can help me with this?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!