How do I return a date based off of a numerical value?

cfischer
cfischer
edited 12/09/19 in Formulas and Functions

Hi Smartsheets Community, 

I have 12 columns that each represent one month and in those columns there are numbers (not dates). Every month I will enter that month's financial data. I want to write a formula that tells me which month was the last month to be updated and I want it to return that month's date (vs. a text value or # value).

For example, I want the formula to say if the # in the December column is not 0, then return the date 12/31/2018, but if it is zero then check if the # in the November column. If the # in the November column is not 0, then return the date 11/31/2018, but if it is zero then check the # in the October column, and so on and so forth.

Right now I have written a formula that returns text by writing If([Dec.]1 <>0, "Dec. 2018", if([Nov.]1 <>0, "Nov. 2018, if([Oct.]1<>0, "Oct. 2018" and so on and so forth until it gets to Jan. This works but returns a text value and I want it to return a date value (e.g., it returned Dec. 31 but I want it to return 12/31/2018 and have Smartsheets recognize that as a date). 

Does anybody know how to do this? 

Thanks! 

Tags:

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!