Time Formats - Number formats - Rethinking the UX and function

Ezra
Ezra ✭✭✭
edited 12/09/19 inFormulas and Functions

Having an entire dialog for text and number formats would make much more sense to me.. combine the currency, percentage, thousands and decimal places into it's own dialog box.

We really NEED a way to save and modify the formatting of common things. So as to have them available and globally modifiable across workspaces.

Use case:

  • People need to punch into and out of a task.
  • the "start hour" may be 07 and the "start minute" might be 04
  • currently Smartsheet adds an apostrophe to the beginning of each
  • so I'm essentially trying to use '07 and '04 as numbers and any formulas that use this information will break.
  • I change the text/number formatting from Hour-military (00-23) to Hour-standard (12am - 12pm) within a dialog box and save the format to the workspace as "TaskHourFormat"
  • Now, if a user inputs 04, it's understood to be 4am, if they enter 6p it's understood by Smartsheet to be 6pm

Advanced text formatting (like making a "Title" format that capitalizes and bolds letters and bolds numbers) and the ability to use the same format across the workspace would really make this a more polished platform.

If any of this is way off-base or just doesn't make sense, let me know. (workspace-wide conditional formatting would be very useful too)

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Ezra,

    Great ideas!

    Please submit an Enhancement Request when you have a moment.

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    These are good ideas as I understand them, and I second Andree's suggestion of putting in an enhancement request.

    I will add though that while it may take some extra work and is not necessarily "workspace wide", there are workarounds that are actually"relatively"straightforward for calculating time and converting the numbers that have a preceding apostrophe (which I personally have only ever seen when adding data via mobile) into useable numbers.

  • Ezra
    Ezra ✭✭✭

    Paul,

    Enhancement Req. is in.

    I could have sworn someone solved this by adding a MIN() condition to the formula that adds hour and minutes/60 together. Maybe it was just a dream... I can't find mention of it anywhere.

    here's a sheet I threw together to test it. Just remove both of the *\/\/ROBOT\/\/* from the url:

    https://app.smartsheet.com/b/pub*\/\/ROBOT\/\/*lish?EQBCT=9eacd64ea32f4526*\/\/ROBOT\/\/*b98cf2f8e77b70bb

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Accounting for a leading 0 is actually pretty straightforward. It also isn't too hard to set up an extra column to convert am/pm 12 hour input to 24 hour time for easier calculations.

    I'll have to dig through my notes, but I have a few sheets that will turn the result back into an HH:MM format and can account for overlap into the next day(s).

    One thing I will definitely note is this...

    While there may be monster formulas out there that can do all of the calculations in one fell swoop, they are just that. Monsters. I have found that it is MUCH easier to break the calculations down and work on them bit by bit. It also provides for easier maintenance if something should break.

    While a lot of people will say that calculating time in SS is a royal pain, as long as you take your time and work on each section individually (testing, tweaking, more testing and more tweaking) before moving on to the next part, it can actually be done rather painlessly (but still tediously).

  • Ezra
    Ezra ✭✭✭

    Silly me...I knew it was a simple fix. (this is if your hours and minutes are in different columns.

    =VALUE([Start Hour]1) + (VALUE([Start Minute]1) / 60)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Haha. We all miss the little things sometimes. If I can find the time to dig through my old notes and sheets, I'll post some examples on here of what I personally have done in regards to time calculations.

  • LGoller
    LGoller ✭✭✭

    这是另一种想法。您可以使用-建造的in time stamps for created and modified and use a form.

    The form has a dummy field that collects the created time stamp. You then create an Update Request that emails the user, that says, Click when you want to punch out, where you record the modified time stamp. Then you lock the row, automatically to prevent further changes.

    You can then run formulas such as FIND, RIGHT, LEFT, or VALUE to the time stamps to figure out the actual time, and then the number of hours or whatever else you need to know from the time stamps.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Have you experimented with Templates and "Save as New"? Have you looked into the Control Center?

  • Ezra
    Ezra ✭✭✭

    Paul,

    wasn't trying to ignore you... yes, just about done building my blueprint in SCC. So many "I wish it could..." hurdles like reports not updating unless 'touched' and various other fiddly things.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    No worries at all. I don't have any experience with the Control Center, but it sounds like you are on your way to a solution. Best of luck!

  • Paul Newcome,


    If you could post the solution for HH:MM I would appretiate much! Thank you!

  • Ezra
    Ezra ✭✭✭

    Here's the columns and formulas I've used:

    Column NameFormatand/orFormula


    Taskprimary column

    Work Tickettext/number

    Assigned Tocontact list - used in resourcing

    Day=IF(Weekday@row = 1, "s", IF(Weekday@row = 2, "M", IF(Weekday@row = 3, "T", IF(Weekday@row = 4, "W", IF(Weekday@row = 5, "R", IF(Weekday@row = 6, "F", IF(Weekday@row = 7, "S", "")))))))

    Datedate format

    Start Hourtext/number

    Start Minutetext/number

    End Hourtext/number

    End Minutetext/number

    Start Time=VALUE([Start Hour]@row) + (VALUE([Start Minute]@row) / 60)

    End Time=VALUE([End Hour]@row) + (VALUE([End Minute]@row) / 60)

    Total Time=[End Time]@row - [Start Time]@row

    Total Hours=INT([Total Time]@row)

    Total Minutes=([Total Time]@row - [Total Hours]@row) * 60

    Weekday=IFERROR(WEEKDAY(Date@row), "")

    Date Enddate format

    Durationhidden and empty - used in dependancies for gantt

    Work=[Total Time]@row- used in dependancies for gantt

    Allocationtext/number - used in dependancies/resourcing

    Duration Hours=ABS(IF(Duration@row = "0+", 0, Duration@row)) * 8

    Predecessorshidden and empty - used in dependancies for gantt


    Then, make a form for you/users to enter info.

    You can experiment with adding sheet summary fields based on users or projects or dates...

    I like to give the sheets some color - so adding conditional formatting for:

    • End Houris not blank and less than 12make the End Hour and End Minute columns green
    • Start Houris not blank and less than 12make the Start Hour and Start Minute columns green
    • Total Timeis greater than 8make the entire row blue
    • Total Timeis greater than 4make the entire row light blue

    have fun!

  • Thank you very much Ezra!!! It is really helpful!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
Hi, <\/p>

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":26,"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":25,"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":"

\n \n https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula\n <\/a>\n<\/div>\n

Hi, <\/p>

I hope you're well and safe!<\/p>

Try something like this.<\/p>

=IF([Date Reported]@row <> \"//www.santa-greenland.com/community/discussion/44746/\", IF([Resolution Date]@row = \"//www.santa-greenland.com/community/discussion/44746/\", NETDAYS([Date Reported]@row, TODAY()), NETDAYS([Date Reported]@row, [Resolution Date]@row)))<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic weekend!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions