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.

time calculation

Reiner Sackmann
Reiner Sackmann ✭
edited 12/09/19 inArchived 2015 Posts

Hi there,

I am a newby here and I must say that smartsheet looks good.

The only thing I am definetly missing is a way to calculate time

这就是我需要说明:

Start End Duration h Price per hour Price total

09:00 10:30 1,5 100,00 150,00

Start and End time can be put in manually

Duration must be calculated automatically ( with a possiblity to round to the the next 10 Minutes, or quarter of a hour )

Price per hour should be a preset

Price total should be calculated automatically

Is there a way to handle this in Smartsheet?

Your help would be appriciated.

regards

Reiner

Tags:
· Share on Facebook Share on Twitter

Comments

  • Joel Johnson
    Joel Johnson ✭

    As far as I know Smartsheet doesn't calculate times very well. So maybe someone else has better knowledge than I. But here is a work around.

    First Start with your Start Time and End Times and use 2 place decimals to indicate time. For example 8:30 is now 8.30 or 9:25 is 9.25.

    Make two more columns ST Conversion & ET Conversion these columns will convert the time into proper decimal format.

    Use the equation for ST Conversion:
    =ROUND([Start Time]1) + (([Start Time]1 - ROUND([Start Time]1)) * 100) * (1 / 60)

    Use the Equation for ET Conversion:
    =ROUND([End Time]1) + (([End Time]1 - ROUND([End Time]1)) * 100) * (1 / 60)

    In Duration use the Equation:
    =IF([ET Conversion]1 > [ST Conversion]1, [ET Conversion]1 - [ST Conversion]1, ([ET Conversion]1 + 12 - [ST Conversion]1))

    This equation takes into account the possibility of going past noon or midnight to calculate the time laps. If you have jobs that go over 12 hours you will need a different equation. Or you can convert it to military time.

    Once you have the duration you can easily find the total price.

    One note: it may be useful if several people are using this sheet to make the start and end times controlled by a drop down menu so that it isn’t accidentally messed up by someone putting a decimal higher than 0.60.

    See it working in the image below. Good Luck

    time calcs.PNG

    · Share on Facebook Share on Twitter
  • Zack S
    Zack S Employee

    Hello Reiner,

    Currently we don't have a way to link tasks to time, or make calculations on time, but I'll add your vote for this capability to our enhancement request list.

    Joel mentioned some great workarounds above with different ways to conver calculations into time.

    -Zack

    · Share on Facebook Share on Twitter
  • Patrick Lawler
    Patrick Lawler ✭
    edited 10/26/15

    Hi Reiner,

    This exact problem can be solved using Azuqua, an integration partner of Smartsheet. I work for Azuqua and the logging timestamp in a column based on a checkbox being marked (checked) is one of the many scenarios that we enable inside of Smartsheet.

    Using Azuqua you can set up a monitor for a change to column check for when the checkbox for "Start" has been marked and then log timestamp / date will into the column you specify.

    With this solution you woud need to set up a monitor for both the start and finish checkboxes and then you would receive a duration based on the subtraction of those two values, which you can use to complete your outlined scenario above.

    If you want to learn more please visit our app listing at Smartsheet here:

    //m.santa-greenland.com/apps/azuqua

    Please feel free to follow up with any questions.

    -Patrick

    · Share on Facebook Share on Twitter
  • Dominic Chan
    Dominic Chan ✭✭
    edited 03/18/16

    I am very interesdted to measure duration by time input:

    Start Time: 10:00 AM

    End Time: 14:26 PM

    Duration: 4:26

    Thanks!

    Dominic

    · Share on Facebook Share on Twitter
  • Travis
    Travis Employee

    Dominic, this can be done with the following formula.

    This formula looks (and is!) intimidating but its easy to use. Just paste it into a text editor and use FIND & REPLACE to replace the start and end date cell references with references from your sheet. It will require you to designate am/pm.

    Here it is!

    = INT(((((左(EndTime1,找到(”:“EndTime1) - 1)= "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) + ":" + IF(((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) - INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) * 60) < 10, "0") + ((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) - INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) * 60)

    And here's an example of it in action:

    https://app.smartsheet.com/b/publish?EQBCT=a820445951964445a795591bee66e3d1

    · Share on Facebook Share on Twitter
  • Morri Young
    Morri Young ✭
    edited 03/19/16

    Travis, this is genius.. Dont care if it works or not, it looks fantastic!

    · Share on Facebook Share on Twitter
  • Randy Burtis
    Randy Burtis ✭

    Travis,

    Our event management company has been waiting so long for a way to calculate time for detailed event programs so I was really excited about your formula. However... I don't see and can't locate using the "Find" tool, any date references in your formula. What am I missing?

    · Share on Facebook Share on Twitter
  • Travis
    Travis Employee
    edited 03/25/16

    Randy, here are the two date cell references for the Start and End time:

    StartTime1

    EndTime1

    · Share on Facebook Share on Twitter
  • Kelly Poirier
    Kelly Poirier ✭

    Hey Travis,

    Do you have a way to do that with neg hours? on a 24h clock?

    StartTime = 14:00

    EndTime = 10:00

    Duration = 20 hours?

    · Share on Facebook Share on Twitter
  • Kate French
    Kate French ✭
    edited 06/09/16

    Hi Kelly,

    Currently hourly timestamps for start and end times aren't supported, but this is on our Enhancement Request list and I've added your vote for it. Thanks for your feedback!

    Kate

    · Share on Facebook Share on Twitter
  • Shirley Hattayer
    Shirley Hattayer ✭✭✭

    Add me to the list.

    That October 15, 2015 solution above does not work. If I do 7.00 and 7.30 it gives me a different calculation than 8.00 and 8.30.

    · Share on Facebook Share on Twitter
  • Raj Dhillon
    Raj Dhillon ✭

    Ditto, we need this at our company for circa <200 PMs and looking at Smartsheets to manage Technology projects. We need to manage projects to the minute in some cases, through specfic weekends.

    Here's whats missing from smartsheets and stopping our adoption and reverting to MS Project:

    - Time data handling/column:- and calculations to set what time a task might be done or perform time calculations.

    -非工作日例外:我们一些weeke工作nds, after work hours, so need to manage working weekends by exception.

    - No baselines:- we need multiple baselines to track variance against causes.

    Cheers,

    Raj

    · Share on Facebook Share on Twitter
  • Dave Gordon
    Dave Gordon ✭✭

    Hi Travis,

    I am using Patrica @ Azuqua's solution for automatically populating the start/end date/time with the ModifedAt timestamp - seehttps://www.youtube.com/watch?v=ouolvEfVh_A 

    I now need to coverts this to HH:M:SS but the example you reference above (https://app.smartsheet.com/b/publish?EQBCT=a820445951964445a795591bee66e3d1)shows an #INVALID VALUE.

    Can you advise how to correct?

    Many thanks

    Regards

    Dave

    · Share on Facebook Share on Twitter
  • KatieTMBA
    KatieTMBA ✭

    Travis,

    I love the idea, though it's not working in my sheet. How must AM or PM be designated for the formula to populate correctly?

    It also doesn't seem to auto-update the formula to look at the correct row when a new row is added to the sheet.

    Thanks!

    KT

    · Share on Facebook Share on Twitter
  • daniel68616
    daniel68616 ✭✭✭

    Hi Travis,

    Not sure if you noticed this yet...I was dealing with the same issue and realized that the cell reference has no brackets. Once I added them the formula worked...example...instead of=INT((((IF(LEFT(End Time, FIND(":", End Time) - 1)...use find and replace all to make start and end times look like this...=INT((((IF(LEFT([End Time]1,FIND(":",[End Time]1) - 1)

    Hope that helps

    · Share on Facebook Share on Twitter
This discussion has been closed.

Categories

HAS() will only return true under three conditions.<\/p>
  1. The cell is a multicontact cell and contains a match for the contact<\/li>
  2. The cell is a multiselect dropdown and contains a match<\/li>
  3. The cell is not a multicontact or multiselect dropdown and contains ONLY and EXACTLY the text you are matching against. <\/li><\/ol>

    I am assuming your row in question is a standard Text\/Number cell? If so, you will need to get somewhat creative with some CONTAINS() statements.<\/p>

    This will narrow down your matches to only cells that contain a space both before and after TAC. If, however, TAC can appear as the first or last \"word\" in the cell, it would not match in that circumstance as there would only be a space on one side.<\/p>

    =IF(CONTAINS(\" TAC \", [Column Name1]@row), 1, 0)<\/p>


    <\/p>

    If TAC can appear as the first or last \"word\" in the cell, this will cover those possibilities as well.<\/p>

    =IF(OR(CONTAINS(\" TAC \", [Column Name1]@row), LEFT([Column Name1]@row, 4) = \"TAC \", RIGHT([Column Name1]@row, 4) = \" TAC\"), 1, 0)<\/p>


    <\/p>

    If it is possible that TAC will be the exact entry of the cell, you will need an additional statement to cover that, as there would not be spaces on either side. This will add that option.<\/p>

    =IF(OR(CONTAINS(\" TAC \", [Column Name1]@row), LEFT([Column Name1]@row, 4) = \"TAC \", RIGHT([Column Name1]@row, 4) = \" TAC\", [Column Name1]@row = \"TAC\"), 1, 0)<\/p>


    <\/p>

    If there are other situations you may have in your sheet, i.e., TAC inside parenthesis, or before or after punctuation, etc, you will additional statements to include those options as well.<\/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":109146,"type":"question","name":"Identify Workflow from ID?","excerpt":"Good morning, got a random one. Is there any way to identify what sheet or workflow an automation ran from? I got a rogue automation this morning, and I don't know what sheet it lives in. However, I noticed at the bottom of the email, there is an ID number, similar to a sheet or row ID. Is there any way, maybe with the…","snippet":"Good morning, got a random one. Is there any way to identify what sheet or workflow an automation ran from? I got a rogue automation this morning, and I don't know what sheet it…","categoryID":321,"dateInserted":"2023-08-18T14:25:47+00:00","dateUpdated":null,"dateLastComment":"2023-08-19T08:29:31+00:00","insertUserID":137019,"insertUser":{"userID":137019,"name":"Samuel Mueller","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Samuel%20Mueller","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B9G8B70WXQWC\/nKTI46EH9WQOM.jpg","dateLastActive":"2023-08-18T20:30:29+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers"},"updateUserID":null,"lastUserID":161820,"lastUser":{"userID":161820,"name":"Hamza1","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Hamza1","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-19T08:37:00+00:00","banned":0,"punished":0,"private":false,"label":"Moderator"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":45,"score":null,"hot":3384804918,"url":"https:\/\/community.smartsheet.com\/discussion\/109146\/identify-workflow-from-id","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109146\/identify-workflow-from-id","format":"Rich","tagIDs":[227,334,448],"lastPost":{"discussionID":109146,"commentID":391519,"name":"Re: Identify Workflow from ID?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391519#Comment_391519","dateInserted":"2023-08-19T08:29:31+00:00","insertUserID":161820,"insertUser":{"userID":161820,"name":"Hamza1","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Hamza1","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-19T08:37:00+00:00","banned":0,"punished":0,"private":false,"label":"Moderator"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/EYR2B79VH1AY\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-18T19:37:53+00:00","dateAnswered":"2023-08-18T19:10:30+00:00","acceptedAnswers":[{"commentID":391468,"body":"

    Yeah. That does sound like it could take awhile. Haha. And if you knew the sheet I'd, you'd be able to just jump to it in SS instead of needing the api.<\/p>


    <\/p>

    Maybe a product enhancement request to expand the workflow portion of the api to include the sheet id is in order.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":227,"urlcode":"api-and-developers","name":"API and Developers"},{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":448,"urlcode":"workflows-in-smartsheet","name":"Workflows in Smartsheet"}]},{"discussionID":109170,"type":"question","name":"COUNTIFS formula with 3 criteria (2 of the criteria have formulas)","excerpt":"Hi - I am trying to create a COUNTIFS formula using three criteria in the same sheet - Status (I just want to count the Open items), Impacted IT Projects (which is another dropdown of project names to select from), and Severity (this column displays a \"High\", \"Medium\", or \"Low\" value based on the calculated score in column…","snippet":"Hi - I am trying to create a COUNTIFS formula using three criteria in the same sheet - Status (I just want to count the Open items), Impacted IT Projects (which is another…","categoryID":322,"dateInserted":"2023-08-18T20:29:26+00:00","dateUpdated":null,"dateLastComment":"2023-08-18T21:13:12+00:00","insertUserID":165367,"insertUser":{"userID":165367,"name":"Shari D","url":"https:\/\/community.smartsheet.com\/profile\/Shari%20D","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-18T21:12:12+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163506,"lastUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-18T21:34:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":91,"score":null,"hot":3384789758,"url":"https:\/\/community.smartsheet.com\/discussion\/109170\/countifs-formula-with-3-criteria-2-of-the-criteria-have-formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109170\/countifs-formula-with-3-criteria-2-of-the-criteria-have-formulas","format":"Rich","lastPost":{"discussionID":109170,"commentID":391505,"name":"Re: COUNTIFS formula with 3 criteria (2 of the criteria have formulas)","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391505#Comment_391505","dateInserted":"2023-08-18T21:13:12+00:00","insertUserID":163506,"insertUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-18T21:34:29+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-18T21:12:26+00:00","dateAnswered":"2023-08-18T21:01:48+00:00","acceptedAnswers":[{"commentID":391499,"body":"

    @Shari D<\/a> My apologies, should have been HAS<\/p>

    =COUNTIFS(Status:Status, \"Open\", Severity:Severity, \"High\", [Impacted IT Projects]:[Impacted IT Projects], HAS(@cell, \"IT Project Name 1\"))<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&siteSectionID=0&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 Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

    Trending Posts

    • How to check a box if exact text is found within a string of text

      Hello, I am trying to place a check in a box when some exact text is found within a string of words. I am looking to place a check when "TAC" is found in a string of words. Here's the formula I am using, but its not working. No error message, but is not selecting the checkbox appropriately. =IF(HAS([Column Name1]@row,…

    • Identify Workflow from ID?

      Good morning, got a random one. Is there any way to identify what sheet or workflow an automation ran from? I got a rogue automation this morning, and I don't know what sheet it lives in. However, I noticed at the bottom of the email, there is an ID number, similar to a sheet or row ID. Is there any way, maybe with the…

    • COUNTIFS formula with 3 criteria (2 of the criteria have formulas)

      Hi - I am trying to create a COUNTIFS formula using three criteria in the same sheet - Status (I just want to count the Open items), Impacted IT Projects (which is another dropdown of project names to select from), and Severity (this column displays a "High", "Medium", or "Low" value based on the calculated score in column…