Formula to calculate End Date and Time based on Start Date, Time and Task duration
Hello,
I'm looking for a formula to calculate the End Date and Time (MM/DD/YYYY)( HH:MM AM/PM) based on the Start Date and Start Time entered on a form using the Task standard routing time.
Best Answers
-
Could you share the formulas in your sheet? I'm still getting errors.
-
The formulas I have provided are copy/pasted from my sheet based on the columns in my above screenshot.
The error in your first screenshot (Form 4) should be because of your column type. Make sure the [Completion Date] column is a date type column.
Make sure your column names and types match mine exactly, and you should be able to copy/paste. Once you have the formulas in place, you can change your column names and the formulas will automatically update. Here is a list of the columns from my above screenshot in order from left to right, their column type, and their formulas/input:
[Actual Clean Start Date]:
date
Manual Entry
[Actual Clean Start Time]:
text/number
Manual Entry
[Standard Clean Routing (Hrs)]:
text/number
Manual Entry
Duration:
text/number
=(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row
[Completion Date]:
date
=[Actual Clean Start Date]@row + INT(INT(Duration@row) / 24)
[Completion Time]:
text/number
=MOD(INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row), 24) + IF(MOD(INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row), 24) <> 12, IF(CONTAINS("p", IF(MOD(INT(Duration@row), 24) >= 12, "pm", "am")), -12), IF(CONTAINS("a", IF(MOD(INT(Duration@row), 24) >= 12, "pm", "am")), 12)) + ":" + IF(((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row - INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row)) * 60 < 10, "0") + ((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row - INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row)) * 60 + IF(MOD(INT(Duration@row), 24) >= 12, "pm", "am")
[Expected Clean Completion Date]:
text/number
=[Completion Date]@row + " " + [Completion Time]@row
Answers
-
I have some ideas, but I will have to get back to you. Calculations involving time in Smartsheet can get a little tricky, so I want to build a mockup to make sure I get it right.
-
Ok. You are going to want to add a column that for these formulas I called "Duration". In this column, you will use:
=(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row
In the Completion Date column:
=[Actual Clean Start Date]@row + INT(INT(Duration@row) / 24)
In the Completion Time column:
=MOD(INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row), 24) + IF(MOD(INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row), 24) <> 12, IF(CONTAINS("p", IF(MOD(INT(Duration@row), 24) >= 12, "pm", "am")), -12), IF(CONTAINS("a", IF(MOD(INT(Duration@row), 24) >= 12, "pm", "am")), 12)) + ":" + ((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row - INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row)) * 60 + IF(MOD(INT(Duration@row), 24) >= 12, "pm", "am")
-
Hi Paul,
Thank you for answering. I am still pretty new to Smartsheet and after reading numerous posts about the time function, I knew this was going to be troublesome. The time for the 1st entry worked but I still need the date, especially if the clean time goes over 24 hours. The 2nd entry gave me 6:0pm, which also does not include the date. Maybe I placed the duration column in the wrong place. Ideally, we would enter in the Clean start time, and the Standard Clean Routing (Hrs) and the calculation would give us the expected Completion date and time in the same column if possible but if not that is fine too.
-
@Shanelle The date solution is in my above post wedged between the two larger formulas. For 6:0pm... I thought I had taken care of that (minutes under 10). I'll dig in and get back to you with that fix shortly.
-
@Shanelle Here is your fix for minutes under 10... I also just saw where you wanted date and time in the same column. You can combine the date and time formulas into one with a space in between like so:
=[Actual Clean Start Date]@row + INT(INT(Duration@row) / 24) + " " + MOD(INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row), 24) + IF(MOD(INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row), 24) <> 12, IF(CONTAINS("p", IF(MOD(INT(Duration@row), 24) >= 12, "pm", "am")), -12), IF(CONTAINS("a", IF(MOD(INT(Duration@row), 24) >= 12, "pm", "am")), 12)) + ":" + IF(((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row - INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row)) * 60 < 10, "0") + ((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row - INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row)) * 60 + IF(MOD(INT(Duration@row), 24) >= 12, "pm", "am")
-
Hi Paul,
The formula works for the most part but I'm still getting a couple of weird numbers.
-
Can you provide examples?
-
I attached screen shots in my post but I don't see them when I actually post the comment.
-
I'm trying this again. Not sure why nothing is uploading.
-
I don't see any screenshots either. I just saw this morning where someone else wasn't able to post screenshots. It ended up being a browser thing. Try switching browsers and see if that works.
Additionally... How do you feel about extra "helper" columns? They can be hidden after setup to keep the sheet looking clean, and it would allow us to break the monster formula down into sections. Breaking it down into sections has quite a few different benefits to include easier troubleshooting and shorter formulas overall since we can use cell references in place of repeating larger sections.
-
I'm fine with helper columns, as long as it doesn't require a lot of additional manual entry.
-
Ok. The helper columns would all contain formulas. You would only have to enter them once, dragfill them down to cover all of the rows already entered, and then everything will be automated and autofill should grab them when new rows are added.
So I replicated your data from your PDF. That is really odd behavior for sure, and I am not certain why it is acting that way. I was able to get the desired results by leaving the date and the time in separate columns then using cell references to combine them like so:
=[Completion Date]@row + " " + [Completion Time]@row
-
Could you share the formulas in your sheet? I'm still getting errors.
-
The formulas I have provided are copy/pasted from my sheet based on the columns in my above screenshot.
The error in your first screenshot (Form 4) should be because of your column type. Make sure the [Completion Date] column is a date type column.
Make sure your column names and types match mine exactly, and you should be able to copy/paste. Once you have the formulas in place, you can change your column names and the formulas will automatically update. Here is a list of the columns from my above screenshot in order from left to right, their column type, and their formulas/input:
[Actual Clean Start Date]:
date
Manual Entry
[Actual Clean Start Time]:
text/number
Manual Entry
[Standard Clean Routing (Hrs)]:
text/number
Manual Entry
Duration:
text/number
=(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row
[Completion Date]:
date
=[Actual Clean Start Date]@row + INT(INT(Duration@row) / 24)
[Completion Time]:
text/number
=MOD(INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row), 24) + IF(MOD(INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row), 24) <> 12, IF(CONTAINS("p", IF(MOD(INT(Duration@row), 24) >= 12, "pm", "am")), -12), IF(CONTAINS("a", IF(MOD(INT(Duration@row), 24) >= 12, "pm", "am")), 12)) + ":" + IF(((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row - INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row)) * 60 < 10, "0") + ((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row - INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) / 60) + [Standard Clean Routing (Hrs)]@row)) * 60 + IF(MOD(INT(Duration@row), 24) >= 12, "pm", "am")
[Expected Clean Completion Date]:
text/number
=[Completion Date]@row + " " + [Completion Time]@row
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
