How to recreate a payment month Excel formula in Smartsheet

SteCoxy
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.


Best Answer

Answers

  • Paul Newcome
    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
    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
    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
    Paul Newcome ✭✭✭✭✭✭
    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.


  • SteCoxy
    SteCoxy ✭✭✭✭✭✭

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!