Formula to calculate End Date and Time based on Start Date, Time and Task duration
Answers
-
Shanelle ✭✭
@Paul NewcomeThank you so much!! It works! I did have the wrong column type for the completion date.
-
Paul Newcome ✭✭✭✭✭✭We finally got it working! That's great! Happy to help.️
不要感觉不好阿布t the column type. I do it to myself all the time. Hahaha.
-
Lyn Pringle ✭✭✭
@Paul NewcomePlease can you help me. :)
I have made use of the 24hr formula to calculate the number of hours worked - which works like a bomb, thank you - however not able to figure out how to create a running total.
Do you have a special formula to autosum the number of hours worked.
My sheet looks like this
-
Paul Newcome ✭✭✭✭✭✭
@Lyn PringleIf you are using the solution above, you are going to want to reference the Duration Column. That houses the numerical values that can be used in math based calculations.
-
Zevy L ✭@Paul Newcome, can you help me with a similar question?
If I separate columns, one for date and one for time, is there a way to set a reminder let's say 24 hours after the time indicated across all columns? So for instance, I have row #1 entered as June 10, 10:00am and row #2 entered as June 20, 11:00am. Can I set a a 24 reminder so that row #1 will remind me at and row #2 will remind me on June 21, 11:00am?
-
Paul Newcome ✭✭✭✭✭✭
@Zevy LIf I remember correctly, there is actually a thread where this exact thing was discussed. Basically what you will need to do is convert the time into a number and then set up 24 automations (one for each hour) with the trigger being date based and the condition being the time.
You can use this to pull the hour number and have it converted to a 24 hour base so that you do not need to build additional criteria for AM and PM into your Automation:
=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + IF(CONTAINS("p", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) = 12, -12))
-
Zevy L ✭
Thank you, Paul! How do I "pull the hour number and have it converted to a 24 hour base"?
-
Zevy L ✭
Paul, I just tried setting it up according to you instructions. If the time is set for say 1pm, will I get the reminder at 1pm? The automation doesn't seem that way... (see screenshot attached
).
-
Paul Newcome ✭✭✭✭✭✭
No. You would need to change the time at the bottom of the Trigger portion. You would also need to change the trigger to run off of a date field and select the column that contains the dates that the reminders would be sent.
-
Mindfull ✭✭保罗,我也是新的内容。我也有同样的problem statement, I want to get a date and time based on the given start date time and total hours spent.I tried the formula suggested by you but I could not accomplish it.
-
Hello - i am trying to create this same type of formula, but am getting some funny errors and not producing date values. Any ideas on how to resolve?
I thought it would also be helpful if i included my formulas:
DR Actual Start:Date
DR Actual Start Time: Text
Actual Duration:Text
Duration:=(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [DR Actual Start Time]2), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2
Completion Date:=[DR Actual Start Time]@row + INT(INT([Duration 1]@row) / 24)
Completion Time:=MOD(INT((VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [DR Actual Start Time]2), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2), 24) + IF(MOD(INT((VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [DR Actual Start Time]2), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2), 24) <> 12, IF(CONTAINS("p", IF(MOD(INT([Duration 1]2), 24) >= 12, "pm", "am")), -12), IF(CONTAINS("a", IF(MOD(INT([Duration 1]2), 24) >= 12, "pm", "am")), 12)) + ":" + IF(((VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [DR Actual Start Time]2), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2 - INT((VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [DR Actual Start Time]2), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2)) * 60 < 10, "0") + ((VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [Duration 1]4), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2 - INT((VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [DR Actual Start Time]2), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2)) * 60 + IF(MOD(INT([Duration 1]2), 24) >= 12, "pm", "am")
Expected Completion Time:=[Completion Date]@row + " " + [Completion Time]@row
-
Ben Goldblatt EmployeeIt sounds like you're looking to be able to enter a start date, time, and duration to then have the actual end date and time calculated automatically. You may be able to leverage theDependency functionalityfor the date automation, but you would need to use formulas for the time calculations.@Paul Newcomemay be the best resource for this in looking through this thread (and feel free to chime in here Paul if you have a moment), but I highly recommendSubmitting an Enhancement Requestto see about having built-in "time tracking" features implemented in a future release.
You may also want to look into ourPro Desk servicesfor coaching on these types of formulas. If you're not familiar with this, Pro Desk delivers practical one-on-one coaching sessions with a Smartsheet expert (30 minutes per session, 10 sessions per year) to accelerate your setup time and help you optimize your projects, programs and processes. A Pro Desk expert will provide coaching, guidance, best practices, and resources for further development.
我希望这可以帮助!
Thanks,
Ben
-
Paul Newcome ✭✭✭✭✭✭
@Ben GThanks for the tag!
@Brenna SaundersTake a look through this threadHERE. There may be something you can use. I do remember helping someone set something very similar up at some point, but I can't remember if it is in the time thread or not.
If not... Feel free to let me know. I am crazy busy with work at the moment, so it may take a little while to put something together, but it can be done.
If I understand correctly... You basically want to manually enter your start date, start time, and duration and it will automatically calculate your end date and end time?
-
mukul ✭
Hi@Paul Newcomeand Experts,
I am new to the smart sheet and the community, so I need assistance calculating the End time based on the following conditions: Start time + Duration, as shown on this sheet, Duration can be expressed in days, hours or minutes (e.g. 1 day, 1 hour or .1 hour, or 60 minutes), factor 8 working days.
I tried using the logic / formula from this published sheet, giving me the error shown above when adding duration as 1 hour or .1h, or 60 minutes. In addition, it didn't convert to AM or PM based on the duration(perhaps I'm doing some wrong in the formula- not an expert)
Please paste your formula or publish
I really appreciate your quick response and advice, which will help me out
Regards
-
Paul Newcome ✭✭✭✭✭✭
@mukulYou would need to convert your duration into a standard measurement whether that be days, hours, or minutes.
Hours:
=IF(LEFT(Duration@row) = "d", VALUE(SUBSTITUTE(Duration@row, "d", "")) * 24, IF(LEFT(Duration@row) = "m", VALUE(SUBSTITUTE(Duration@row, "m", "")) / 60, VALUE(SUBSTITUTE(Duration@row, "h", ""))))
From there you can use the formulas that will add a number of hours to a time.
帮助Article Resources
Categories
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/67756/formula-to-calculate-end-date-and-time-based-on-start-date-time-and-task-duration/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/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":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":54,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+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-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"
