Convert date field to month text

Deanna Copello

Hello!

My goal: 01/02/2024 returns a value of January or January 2024 in another cell

The column that contains 01/02/2024 is a formula calculating this date. I need to show the month (and year if possible) in text in another column.

Is this even possible?

JamesB

@Deanna Copello Because Smartsheet does not have the selection natively in the Date dropdown for Month Year, you will need to create a long formula.

For the long formula it would be:

if(Month([COLUMN NAME]@row) = 1, Join("January"," ",YEAR([COLUMN NAME]@row)),if(Month([COLUMN NAME]@row) = 2, Join("February"," ",YEAR([COLUMN NAME]@row)),etc.)

You could also use a reference sheet with 2 columns, 1 being month number and 1 being month name, and then use an index/match

=JOIN(INDEX({Month Number},MATCH(MONTH[DATE]@row,{MONTH NAME},0)," ",YEAR([DATE]@row))

Deanna Copello

What am I doing wrong here?

Deanna Copello

If i change the 1 to read 01 or 02, it also comes back with unparseable.

Andrée Starå

Hi @Deanna Copello

I hope you're well and safe!

There are multiple issues, but the proper structure depends on if it's in the same sheet or another.

Is it all on the same sheet?

I hope that helps!

Be safe, and have a fantastic week!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Deanna Copello

It is all on the same sheet in the same cell. the two columns pictured above are right next to each other. I did try the reference to another "source sheet" but that didn't work either.

JamesB

@Deanna Copello

I just recreated your sheet, but I took the Join command out this time, which works.

=IF(MONTH([Mike 1]@row) = 1, "January" + " " + YEAR([Mike 1]@row))

If you want to add more months, then continue with nesting if statements.

=IF(MONTH([Mike 1]@row) = 1, "January" + " " + YEAR([Mike 1]@row),IF(MONTH([Mike 1]@row) = 2, "February" + " " + YEAR([Mike 1]@row)))

Deanna Copello

AMAZING! Thank you SO much! you have no idea how happy the execs are going to be about this!!!!

Brad Schutts

I needed the same thing for merging into a generated document (so instead of 1/2 it would say January 2nd). I use a separate sheet to list numbers down one column. Then subsequent columns could be the months, the dates spelled out (first, second, etc) or even year spelled (twenty nineteen). Then you would have a separate column for Month@row so it only pulls the month.

It'd be the date column, month calculation column, and vlookup column. Your date would be 1/2, the month column would be =month(date@row). Your vlookup column would be =vlookup([Month Calculation]@row,{{separate numbers smartsheet}}, 2,false).

Hopefully that makes sense and is helpful.

Andrée Starå

@Deanna Copello

Happy to help!

I saw that James had answered already!

I'd recommend using the following structure to ensure that it will be in the correct order if you report on it.

=IF([Mike 1]@row <> "", IF(MONTH([Mike 1]@row) = 1, "01 January", IF(MONTH([Mike 1]@row) = 2, "02 February", IF(MONTH([Mike 1]@row) = 3, "03 March", IF(MONTH([Mike 1]@row) = 4, "04 April", IF(MONTH([Mike 1]@row) = 5, "05 May", IF(MONTH([Mike 1]@row) = 6, "06 June", IF(MONTH([Mike 1]@row) = 7, "07 July", IF(MONTH([Mike 1]@row) = 8, "08 August", IF(MONTH([Mike 1]@row) = 9, "09 September", IF(MONTH([Mike 1]@row) = 10, " 10 October", IF(MONTH([Mike 1]@row) = 11, "11 November", IF(MONTH([Mike 1]@row) = 12, "12 December")))))))))))))

Let me know if I can help with anything else!

Best,

Andrée

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

whitney.thomas

Hello,

I need a similar equation as Deanna's above, but i need the formula to convert the Month and Year from the "Created Date" column of a sheet.

From reading the above, i modified it suit as per the below so that it displays as "January 2024":

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

The problem is that for the day 1 of each month, of the next month, it continues the previous months name for a few lines before correcting:

Anyone know how to fix this?

Thanks,

Whitney

KPH

Hi @whitney.thomas

Can you temporarily add a column to your sheet and enter =DATE([Created Date]@row) and let me know what that shows for the problematic rows?

whitney.thomas

Hello @KPH,

Thanks for your suggestion!

The =DATE([Created Date]@row) formula wouldn't work for me for some reason, so i tried =DATEONLY([Created Date]@row) and that worked but didn't fix the issue.

It seems that any value input onto the sheet via form after 1pm of the first day of each month is correct, therefore looks like Smartsheet is getting confused with time zones for the first half of each day 1 for each month.

Hopefully this makes sense? Not sure if there is a fix for this after all.

KPH

Sorry, I did mean DATEONLY. Long day! And yes, you are correct. I wanted to see what the date was displayed as to test my hypothesis that the formula is fine, but the data going into it, is incorrect. As you have seen, that is the issue.

There is a known issue whereby the date is stored in UTC, displayed in local time zone, but uses UTC when used in formula. I don't think the issue will be limited to the first dat of the month, but will be a problem for any day where the form is submitted before 1pm.

There are workarounds which involve taking the text from the created date and making your own date. Then using the date you create in your formula. As you have seen using DATEONLY is not the answer.

To create your own date you need these parts:

Day

=VALUE(LEFT([Created Date]@row + "", 2))

Month

=VALUE(MID([Created Date]@row + "", 4, 2))

Year

=YEAR([Created Date]@row)

You don't need to make all those columns - I've separated them out so you can see how the formula is built).

You combine the three parts in one formula to create the date only yourself (in a column that is date type) :

=DATE(YEAR(Created@row), VALUE(MID(Created@row + "", 4, 2)), VALUE(LEFT(Created@row + "", 2)))