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

cfischer

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! 

Mike Wilday

Hi there, You would want to return a value based on the Date Function. 

Instead of using If([Dec.]1 <>0, "Dec. 2018",

You would write it: IF([Dec.]1<>0, Date(2018, 12, 31),... 

That would return the date 12/31/2018. Just replace each of your text dates with that formula. If you have trouble getting it to work reply and paste the whole formula in here and I'll help you troubleshoot. 

Here is more documentation on the Date Function. 

https://help.smartsheet.com/function/date

 

cfischer

Awesome! Thank you so much for your help! It worked!

Mike Wilday

Awesome! Glad I could be of help!