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, pleaseVisit 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?
-
Travis Employee这是可能的,如果圣嵌套atement. 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 Employee
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 manuallyMANYtimes!
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
-
格伦·弗雷泽 ✭
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.
我已经添加到配方包括“天”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 EmployeeHi 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
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":24,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":24,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
