How do I return a date based off of a numerical value?
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!
Comments
-
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
-
Awesome! Thank you so much for your help! It worked!
-
Awesome! Glad I could be of help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!