How Do I create a value based on a date or date range?

esokolof

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

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