Formula for converting a word into a Date, so I can create a Calendar.

ssuser01

Hello All! Happy Smartsheet Day!

I am in need of some assistance. I created a work log of firm assets that need to be maintained in perpetuity. I want to create a calendar view. In order to do so, I need to establish a start and end date. My end date will be the completion date. However, I need to create a start date based on a Month. I am trying to create a formula that will convert a month word into a date to establish a start date. I would need the formula to encompass every month.

Andrée Starå

https://community.smartsheet.com/discussion/129483/formula-for-converting-a-word-into-a-date-so-i-can-create-a-calendar

Hi,

I hope you're well and safe!

Not sure I follow! Can you elaborate?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

jmyzk_cloudsmart_jp

Hi @ssuser01

It is easier to reference a table like this instead of writing a lot of if statements, like the following;

=IF([Time Frame]@row = "January", DATE(Year#, 1, 1), IF([Time Frame]@row = "February", DATE(Year#, 2, 1),,,,

(Year# refers to the Year value of the Sheet Summary.)

https://app.smartsheet.com/b/publish?EQBCT=ede356f832594ac3be005de0819f4b90

Then, you can use INDEX(MATCH() or VLOOKUP functions to get the month number and create a [Calender Date].

=DATE(Year#, INDEX({Month word to Number : Number}, MATCH([Time Frame]@row, {Month word to Number : Month}, 0)), 1)

https://app.smartsheet.com/b/publish?EQBCT=d70274651bb749abba2fd741fcc5766a

PM_Reeves

Hi @ssuser01,

It's a long one, but here you go

=IF(Month@row = "January", 1, IF(Month@row = "February", 2, IF(Month@row = "March", 3, IF(Month@row = "April", 4, IF(Month@row = "May", 5, IF(Month@row = "June", 6, IF(Month@row = "July", 7, IF(Month@row = "August", 8, IF(Month@row = "September", 9, IF(Month@row = "October", 10, IF(Month@row = "November", 11, IF(Month@row = "December", 12, "ERROR!"))))))))))))

ssuser01

Hi @jmyzk_cloudsmart_jp! Yes, I think you understand where I was going.

Does your formula allow a perpetual date? - The date will change when the next year occurs?

jmyzk_cloudsmart_jp

Hi @ssuser01

If you put a formula like this, the date will change when the next year occurs.

Adrian Mandile CHESS

Hi @ssuser01,

Can I suggest that in your 'Time Frame' column dropdown options, instead of listing "January", "February", "March", etc. that you make it "01 January", "02 February", "03 March", etc. In that way, you would be able to extract the month number as text and include it in your 'Calendar Date' column formula as:

=DATE(Year#. VALUE(LEFT([Time Frame]@row, 2)), 1)

Using numbered months in the 'Time Frame' column, would also have the added benefit of being able to use it as a Grouping column in a report, since it would sort itself naturally – compared to the incorrect alpha sort of just using the month names: April, August, February, January, etc…

ssuser01

Hi Everyone,

Thank you for your thoughts - I am a little confused. Let me show you what I have completed at this time and what I need to complete next:

First I created a formula for Calendar Date that would trigger off of the time frame selected. However, I would like this timeframe to be perpetual as it is a maintenance log that resets at the end of the year.

Here is the formula for the Calendar Date: Question: how do I make this formula perpetual.

=IF(Timeframe@row = "November", "11/1/2024", IF(Timeframe@row = "January", "1/1/24", IF(Timeframe@row = "February", "2/1/24", IF(Timeframe@row = "March", "3/1/24", IF(Timeframe@row = "April", "4/1/24", IF(Timeframe@row = "May", "5/1/24", IF(Timeframe@row = "June", "6/1/24", IF(Timeframe@row = "July", "7/1/24", IF(Timeframe@row = "August", "8/1/24", IF(Timeframe@row = "September", "9/1/24", IF(Timeframe@row = "October", "10/1/24", IF(Timeframe@row = "December", "12/1/24"))))))))))))

Next Step- I want to create a perpetual calendar based on start date and duration resulting in an end date. Currently, I created a Duration of time to complete view based on a selected timeframe. However, I can't seem to add my duration to my Calendar to create a new targeted end date.

Current Targeted Completion Date:

=IF([Duration of Time to Complete review2]@row = "28d", [Calendar Date]@row + 28, IF([Duration of Time to Complete review2]@row = "10d", [Calendar Date]@row + 10, IF([Duration of Time to Complete review2]@row = "7d", [Calendar Date]@row + 7, IF([Duration of Time to Complete review2]@row = "22d", [Calendar Date]@row + 22, IF([Duration of Time to Complete review2]@row = "20d", [Calendar Date]@row + 20)))))

I am trying to create a targeted completion date.

ssuser01

@

ssuser01

@jmyzk_cloudsmart_jp

Your formula below. To make sure I have this correct. I would create a supplemental sheet, however, would I only have one formula as listed below? Then for my formula listed below- I reference the formula below for the year in my main formula- see in yellow?

jmyzk_cloudsmart_jp

Hi @ssuser01

To reference a sheet summary field, you put # at the end of the field's title, like Year#.

=IF(Timeframe@row = "January", DATE(Year#, 1, 1), IF(Timeframe@row = "February", DATE(Year#, 2, 1), IF(Timeframe@row = "March", DATE(Year#, 3, 1), IF(Timeframe@row = "April", DATE(Year#, 4, 1), IF(Timeframe@row = "May", DATE(Year#, 5, 1), IF(Timeframe@row = "June", DATE(Year#, 6, 1), IF(Timeframe@row = "July", DATE(Year#, 7, 1), IF(Timeframe@row = "August", DATE(Year#, 8, 1), IF(Timeframe@row = "September", DATE(Year#, 9, 1), IF(Timeframe@row = "October", DATE(Year#, 10, 1), IF(Timeframe@row = "November", DATE(Year#, 11, 1), IF(Timeframe@row = "December", DATE(Year#, 12, 1)))))))))))))

If you do not want to use the sheet summary field, you can put the YEAR(TODAY()) directly in place of 2024.

=IF(Timeframe@row = "January", DATE(YEAR(TODAY()), 1, 1), IF(Timeframe@row = "February", DATE(YEAR(TODAY()), 2, 1), IF(Timeframe@row = "March", DATE(YEAR(TODAY()), 3, 1), IF(Timeframe@row = "April", DATE(YEAR(TODAY()), 4, 1), IF(Timeframe@row = "May", DATE(YEAR(TODAY()), 5, 1), IF(Timeframe@row = "June", DATE(YEAR(TODAY()), 6, 1), IF(Timeframe@row = "July", DATE(YEAR(TODAY()), 7, 1), IF(Timeframe@row = "August", DATE(YEAR(TODAY()), 8, 1), IF(Timeframe@row = "September", DATE(YEAR(TODAY()), 9, 1), IF(Timeframe@row = "October", DATE(YEAR(TODAY()), 10, 1), IF(Timeframe@row = "November", DATE(YEAR(TODAY()), 11, 1), IF(Timeframe@row = "December", DATE(YEAR(TODAY()), 12, 1)))))))))))))

However, above solution may be some problems as followsIf I enter November, December, and then January in the next year, the date for January is correctly set to 2025/01/01, but the previous year's 2024 is automatically changed to 2025/12/01 in the current formula.A better solution to this would be to take the year data from Created.

=IF(Timeframe@row = "January", DATE(YEAR(Created@row), 1, 1), IF(Timeframe@row = "February", DATE(YEAR(Created@row), 2, 1), IF(Timeframe@row = "March", DATE(YEAR(Created@row), 3, 1), IF(Timeframe@row = "April", DATE(YEAR(Created@row), 4, 1), IF(Timeframe@row = "May", DATE(YEAR(Created@row), 5, 1), IF(Timeframe@row = "June", DATE(YEAR(Created@row), 6, 1), IF(Timeframe@row = "July", DATE(YEAR(Created@row), 7, 1), IF(Timeframe@row = "August", DATE(YEAR(Created@row), 8, 1), IF(Timeframe@row = "September", DATE(YEAR(Created@row), 9, 1), IF(Timeframe@row = "October", DATE(YEAR(Created@row), 10, 1), IF(Timeframe@row = "November", DATE(YEAR(Created@row), 11, 1), IF(Timeframe@row = "December", DATE(YEAR(Created@row), 12, 1)))))))))))))

https://app.smartsheet.com/b/publish?EQBCT=d70274651bb749abba2fd741fcc5766a