Question

I am working on a subscription template. I have very minimal experience with formulas in Excel. I want a formula to use a property called "Subscribed Date" formated as MM/DD?YYYY to calcuate the "Next due date" and it be formated as MM/DD/YYYY also. I have the following code that gives me the correct date but in the format of MMM DD, YYYY. I can't figure out what I am doing wrong. I would appreciate any help!

if(formatDate(prop("Subscribed Date"), "MM/DD/YYYY") == formatDate(now(), "MM/DD/YYYY"), "For Today", if(prop("Recurring") == "Monthly", dateAdd(prop("Subscribed Date"), dateBetween(now(), prop("Subscribed Date"), "months") + 1, "months"),  if(prop("Recurring") == "Yearly", dateAdd(prop("Subscribed Date"), dateBetween(now(), prop("Subscribed Date"), "years") + 1, "years"), prop("Subscribed Date"))))

Answer 1

You always need to include the formatDate() function when you want a date display different than the default.

This just wraps it around your 2nd and 3rd nested if() conditionals so you don't have to add it to each.

if(formatDate(prop("Subscribed Date"), "MM/DD/YYYY") == formatDate(now(), "MM/DD/YYYY"), "For Today", 
    formatDate(
        if(prop("Recurring") == "Monthly", dateAdd(prop("Subscribed Date"), dateBetween(now(), prop("Subscribed Date"), "months") + 1, "months"),  
        if(prop("Recurring") == "Yearly", dateAdd(prop("Subscribed Date"), dateBetween(now(), prop("Subscribed Date"), "years") + 1, "years"), prop("Subscribed Date")))
        ,"MM/DD/YYYY")
)

That final "MM/DD/YYYY" can be switched out for whatever date format you prefer.