Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
How Do I create a value based on a date or date range?
I have a due date column that I am trying to return a specific value from.My fiscal year runs from 10/1/17 through 9/30/18. Is there a formula that I can write that would pull the fiscal year and month and put that in a cell based on said due date? So if a due date is say 12/14/17, I would like it to say Dec FY18 in that new cell. Any suggestions?
Comments
-
This can be tricky because I don't think we can easily specify that a date should be in 'long' or 'text' format yet. However, I've used some formulas to pull out specific parts of date values and convert them to stings...
This will return a month name value for a given date value. In this case it looks at [End date] month number and writes out the month name.
=IF(MONTH([End date]2) = 1, "January", IF(MONTH([End date]2) = 2, "February", IF(MONTH([End date]2) = 3, "March", IF(MONTH([End date]2) = 4, "April", IF(MONTH([End date]2) = 5, "May", IF(MONTH([End date]2) = 6, "June", IF(MONTH([End date]2) = 7, "July", IF(MONTH([End date]2) = 8, "August", IF(MONTH([End date]2) = 9, "September", IF(MONTH([End date]2) = 10, "October", IF(MONTH([End date]2) = 11, "November", IF(MONTH([End date]2) = 12, "December"))))))))))))You could use this along with a formula that calculates your FY[YEAR]. Hope that helps.
-
For converting month numbers to names (and week days to names of day), I set up a table somewhere on the Sheet, usually hidden from the end-user and then use a LOOKUP function to find the value I need.
Craig
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives