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?

esokolof ✭✭
edited 12/09/19 in Archived 2017 Posts

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?



  • Jason Anderson

    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. 


    My Formula Testing Sheet


  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    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.


This discussion has been closed.