How to recreate a payment month Excel formula in Smartsheet

SteCoxy

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.

Paul Newcome

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.

Paul Newcome

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)

SteCoxy

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?

SteCoxy

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

Paul Newcome

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.

SteCoxy

OK great stuff - that's worked nicely thank you!

Paul Newcome

Happy to help. 👍️