How to recreate a payment month Excel formula in Smartsheet
Hoping you might be able to help me with migrating a formula from Excel into Smartsheet.
I'm in the process of recreating a payment tracker solution for some colleagues of mine, but I'm struggling to replicate one of the formulas. The formula is as follows:
=IF(DAY(C4)<=3,TEXT(C4,"yy-mm(mmm)"),TEXT(C4+30,"yy-mm(mmm)"))
The C column is referencing the date column.
The column where this formula is applied to is called "month" which advises what month the payment falls under. As you can see on the 2nd row below, even though the date is 26th April, this would fall under May's payment month.
Best Answer
-
I suggest outputting the date and then formatting the column to short date. That way you can still leverage these actual dates in further calculations if needed.
Answers
-
What's the logic? It looks to me like you want to output a date. If the date is less than or equal to the third of the month then output the date, otherwise output the date plus 30 days?
If so, it would look something like:
=IF(DAY([Date Column]@row)<= 3, [Date Column]@row, [Date Column]@row + 30)
-
Hi Paul,
Thank you for coming back to me here as ever with your expertise.
I believe it relates to which month the payment will fall under, so if the date is less than or equal to the third of the month, then they want the current month to be displayed, otherwise the payment will fall in the following month (hence the +30days).
They don't want a date being displayed, rather they would like it in the format that appears in the screenshot above "yy-mm(mmm)", e.g. 23-10(OCT). I'm not sure if this is possible in Smartsheet?
-
Just to add, I think they'd be happy with just the month being displayed - doesn't necessarily have to be in that exact format
-
I suggest outputting the date and then formatting the column to short date. That way you can still leverage these actual dates in further calculations if needed.
-
OK great stuff - that's worked nicely thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!