Convert the text "yesterday" to a date format.

JacksonElla

I downloaded some issues from Jira and I have a column from there titled "Last Updated". In that column it has the regular date format but also text that reads "Yesterday" or "Today". I figured I could use the TODAY() formula to convert the today dates, but how do I configure that to convert to yesterday? Is it TODAY(-1)?

Additionally how do I write a Column Formula that says "wherever you find the text "yesterday" and "today" convert it to the appropriate date?"

Thank you!

Nick Korna

Hi @JacksonElla,

If "Yesterday" and "Today" are the only text values you end up with, then this formula would do what you're after:

=IF([Last Updated]@row = "Yesterday", TODAY(-1), IF([Last Updated]@row = "Today", TODAY(), [Last Updated]@row))

Ironically, typing Yesterday/Today directly into a date column in Smartsheet will give the relevant date (as will last/previous/next <insert day>) but I don't know if the Jira integration would support that or you'd need to use a helper column with the formula as above.

Nick Korna

Hi @JacksonElla,

If "Yesterday" and "Today" are the only text values you end up with, then this formula would do what you're after:

=IF([Last Updated]@row = "Yesterday", TODAY(-1), IF([Last Updated]@row = "Today", TODAY(), [Last Updated]@row))

Ironically, typing Yesterday/Today directly into a date column in Smartsheet will give the relevant date (as will last/previous/next <insert day>) but I don't know if the Jira integration would support that or you'd need to use a helper column with the formula as above.

JacksonElla

Thanks Nick! I appreciate the help on the formula.