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

✭✭
edited 08/16/24

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.

• ✭✭✭✭✭✭

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

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.)

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)

• ✭✭✭✭

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!"))))))))))))

• ✭✭

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?

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭

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…

CHESS Consulting Australia - Smartsheet Solution Provider Gold Partner
Collaborative | Holistic | Effective | Systems | Solutions

• ✭✭
edited 08/20/24

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.

• ✭✭
• ✭✭

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?

• ✭✭✭✭✭✭

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)))))))))))))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!