Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
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
Comments
-
Did you have any luck figuring out a solution?
-
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.
-
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!
-
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
-
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)
-
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!
-
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?
-
You are missing the MONTH function for May.
Craig
-
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!!!
-
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])
-
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
-
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.
-
DD-MMM-YYYY is essential for globally distributed companies. Please make this a personal settings option that isn't region-specific.
-
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.
Best,
Kara
Categories
- All Categories
- 14 Welcome to the Community
- 10.7K Get Help
- 63 Global Discussions
- 69 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
