How to convert dates to Long Form Date format?

I understand that date formatting is normally set in "Personal Settings" but in order for the SmartSheet Merge to work for a problem I'm trying to solve, I need to convert the date to the long form (February 11th, 2016 instead of 11/02/16.

Is there a script or IF statement that I could use to make the change?

Thanks,

SRC

Find more posts tagged with

Human Resources

Templates

Calendar View

Formulas

Conditional Formatting

Comments

Brian King

Did you have any luck figuring out a solution?

Travis

This is possible with a nested IF statement. It will be a long one, but it is doable.

What you will want to do use the MONTH() DAY() and YEAR() functions.

For example:

=IF(MONTH(cell1) = 1, "January", IF(MONTH(Cell1) = 2, "February", ....etc

Then you can use DAY() to display the day. If you want to add th, nd, rd suffixes, these will need to be defined in nested IF statements.

Finally use YEAR() to get the year.

Concatenate all of these with punctuation and spaces to get the final result.

Keep in mind, the result will be text not a date, so you couldnt use this (for example) in a report Date criteria.

SRC

Thank you, this is helpful. This is to get the date formated so the merge will produce a long date format so we don't have to type it manually. I was going to try to do a strange cross reference in a separate sheet but this is more elegant.

I will try to update this ticket with the final resulting formula for reuse.

Thanks!

SRC

I have played around with the nested formula but I am over my head. Here is what I have so far...

=IF(MONTH(DateC) = 01, "January ", IF(MONTH(DateC) = 02, "February ", IF(MONTH(DateC) = 03, "March ", IF(MONTH(DateC) = 04, "April ", IF(MONTH(DateC) = 05, "May ", IF(MONTH(DateC) = 06, "June ", IF(MONTH(DateC) = 07, "July ", IF(MONTH(DateC) = 08, "August ", IF(MONTH(DateC) = 09, "September ", IF(MONTH(DateC) = 10, "October ", IF(MONTH(DateC) = 11, "November ", "December "))))))))))),DAY(DateC) IF (DAY(DateC) = 01, "st, ", IF (DAY(DateC) = 02, "nd, ", IF (DAY(DateC) = 03, "rd, ", IF (DAY(DateC) = 21, "st, ", IF (DAY(DateC) = 22, "nd, ", IF (DAY(DateC) = 23, "rd, ", IF (DAY(DateC) = 31, "st, ", "th, ",))))))) YEAR(DateC)

I am not sure where I went wrong but if someone can give me some pointers it would be appreciated...

Thanks

Travis

SRC,

You were close, but there are a few issues.

First, you are not referencing any cells. DateC is not a cell - you need a row number associated with it.

If your column name is DateC and the formula is in row 1, then it should be DateC1.

Next, use + to concatenate. "This + That" will come out to "ThisThat".

When using IF statements, the IF needs to be directly next to the rest of the formula.

IF (Date(DateC1).... = incorrect

IF(DATE(DateC1)... = correct

Try this:

=IF(MONTH(DateC1) = 1, "January ", IF(MONTH(DateC1) = 2, "February ", IF(MONTH(DateC1) = 3, "March ", IF(MONTH(DateC1) = 4, "April ", IF(MONTH(DateC1) = 5, "May ", IF(MONTH(DateC1) = 6, "June ", IF(MONTH(DateC1) = 7, "July ", IF(MONTH(DateC1) = 8, "August ", IF(MONTH(DateC1) = 9, "September ", IF(MONTH(DateC1) = 10, "October ", IF(MONTH(DateC1) = 11, "November ", IF(MONTH(DateC1) = 12, "December ")))))))))))) + DAY(DateC1) + (IF(DAY(DateC1) = 1, "st, ", IF(DAY(DateC1) = 2, "nd, ", IF(DAY(DateC1) = 3, "rd, ", IF(DAY(DateC1) = 21, "st, ", IF(DAY(DateC1) = 22, "nd, ", IF(DAY(DateC1) = 23, "rd, ", IF(DAY(DateC1) = 31, "st, ", "th, ")))))))) + YEAR(DateC1)

SRC

Thank you Travis, this works perfectly and you have saved us typing out the date manually MANY times!

I hope the formula you posted will help others out as well.

You da man!

LouSnz

Hello, I am trying to do something similar to this, but coming up with invalid formula:

=IF(MONTH([Invoice Start Date]13) = 1, "January ", IF(MONTH([Invoice Start Date]13) = 2, "February ", IF(MONTH([Invoice Start Date]13) = 3, "March ", IF(MONTH([Invoice Start Date]13) = 4, "April ", IF([Invoice Start Date]13) = 5, "May ", IF(MONTH([Invoice Start Date]13) = 6, "June ", IF(MONTH([Invoice Start Date]13) = 7, "July ", IF(MONTH([Invoice Start Date]13) = 8, "August ", IF(MONTH([Invoice Start Date]13) = 9, "September ", IF(MONTH([Invoice Start Date]13) = 10, "October ", IF(MONTH([Invoice Start Date]13) = 11, "November ", IF(MONTH([Invoice Start Date]13) = 12, "December ")))))))))))) + DAY([Invoice Start Date]13) + (IF(DAY([Invoice Start Date]13) = 1, "st, ", IF(DAY([Invoice Start Date]13) = 2, "nd, ", IF(DAY([Invoice Start Date]13) = 3, "rd, ", IF(DAY([Invoice Start Date]13) = 21, "st, ", IF(DAY([Invoice Start Date]13) = 22, "nd, ", IF(DAY([Invoice Start Date]13) = 23, "rd, ", IF(DAY([Invoice Start Date]13) = 31, "st, ", "th, ")))))))) + YEAR([Invoice Start Date]13)

I am obviously doing something incorrect, any tips please?

J. Craig Williams

You are missing the MONTH function for May.

Craig

Glenn Frazier

They need to make another folmula call that can output the Long Name of the Month from a Date format column..

Would be nice if they could do the same for list the days of the week from a Date column...

And please get a Time formated Column!!!

Joe Williams

I keep going back to this answer because it's been a common request from people on my team. Note to smartsheet staff: you need to have this funtionality natively.

I've added to the formula to include "day" at the beginning. So the final output is something like: "Sunday, Janurary 15th, 2017". For reference the formula is here. Replace [YOURCELL] with whatever cell reference you need.

=IF(WEEKDAY([YOURCELL]) = 1, "Sunday, ", IF(WEEKDAY([YOURCELL]) = 2, "Monday, ", IF(WEEKDAY([YOURCELL]) = 3, "Tuesday, ", IF(WEEKDAY([YOURCELL]) = 4, "Wednesday, ", IF(WEEKDAY([YOURCELL]) = 5, "Thursday, ", IF(WEEKDAY([YOURCELL]) = 6, "Friday, ", IF(WEEKDAY([YOURCELL]) = 7, "Saturday, "))))))) +

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

DAY([YOURCELL]) +

(IF(DAY([YOURCELL]) = 1, "st, ", IF(DAY([YOURCELL]) = 2, "nd, ", IF(DAY([YOURCELL]) = 3, "rd, ", IF(DAY([YOURCELL]) = 21, "st, ", IF(DAY([YOURCELL]) = 22, "nd, ", IF(DAY([YOURCELL]) = 23, "rd, ", IF(DAY([YOURCELL]) = 31, "st, ", "th, ")))))))) +

YEAR([YOURCELL])

Paul Johnson1

I have a similar issue with SS.

While the suggestions above are pretty clever formulae wise it is not really a solution as the result is text based and not a real date.

I am working with a multinational with offices in US and Ireland and need the date to be in numeric/text format, i.e 2nd Jan 2017 so that there is no confusion in the actual date. Similar to excel.

We then wish to derive other dates from this date so it must be a a formal date field not text.

Can you please register this request with SS.

Regards

Paul Johnson

Ramsay Zaki

We too have this problem. Requesting that the official date field be able to display DD-MMM-YYYY (e.g. 04-FEB-2018).

As a multinational company this is a very important feature for us. Too often there is confusion about the dates and this format removes all potential mixups.

Sarah Keortge

DD-MMM-YYYY is essential for globally distributed companies. Please make this a personal settings option that isn't region-specific.

Kara Lumley

Hi all,

Thank you for your patience.

Barring any major issues, we will be releasing the ability to format date columns with 10 new non-ambiguous formats in June.