Formulas for Calculating Time

Paul Newcome

@allison.worley The first part is most likely not working correctly due to a misplaced parenthesis. There is one missing after the second TIME function. You closed the function out, but you forgot to close out that section of things. It looks like you had it in your first comment but must have accidentally dropped it out at some point.

=IF([Run Date]@row = [Run End Date]@row, (TIME([BOT End Time]@row) - TIME([BOT Start Time]@row)) * 1440 / 60)

Andrew Phay

Thanks for putting this thread up. It has been very helpful.

I am trying to put together a vehicle reservation sheet, that has the option to span more than one day. I found the Flagging a Date and Time Overlap sheet in this post, and it works great if there is only one date. I use a Start Date and End Date, so I need to be able to compare over multiple days. At the moment the Overlap field has the below formula in it, modified for fields from your formula. I can't figure out how I would change the formula to flag the row if the vehicle is already reserved for that day, even if it is multiple days.

I have the Start and End Times in a number format for ease of use in this formula. They only go from 700-1700.

=IF(COUNTIFS(Vehicle:Vehicle, Vehicle@row, [Start Date]:[Start Date], [Start Date]@row, [End Time]:[End Time], @cell >= [Start Time]@row, [Start Time]:[Start Time], @cell <= [End Time]@row) > 1, 1)

Paul Newcome

@Andrew Phay Try something like this:

=IF(COUNTIFS(Vehicle:Vehicle, Vehicle@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row, [End Time]:[End Time], @cell >= [Start Time]@row, [Start Time]:[Start Time], @cell <= [End Time]@row) > 1, 1)

Andrew Phay

@Paul Newcome That seemed to work. Thanks for taking the time to reply, and thanks for the thread!

Julie Becker

I wish Smartsheets would hire staff to take the contents provided in this very long and ongoing topic along with the many resolved & archived (with working / approved formulas and examples) topics and put them in a How To guide 🤔along with their online Training Sections as ref. tools that past users had already requested. This would eliminate many of the duplicate topics (maybe), but would also quickly provide solutions with formulas to those of us who are also needing quickly without having to ask / search again. But we all know that wont ever happen lol.. If any of you are ever bored enough to do this, I am sure many of us would be grateful. 😁

Jim B

https://community.smartsheet.com/discussion/comment/445179#Comment_445179

I agree there's a great deal of exceptional advice in this thread but it is tricky to navigate.

Omar de Armas

Hello,

I have a sheet where information is entered from various parts of the world. We need to convert the date and time of an incident (not when the entry was completed) to Pacific Time Zone. To further complicate things we need to take into account Standard time vs. Daylight savings time. The intake form has separate entries for Date, Hour and minutes and I also have a separate sheet with the Time Zone offset to PST and PDT which I bring into the main sheet using Datamesh based on a Region entry for the Time Zone of the input location. I looked at a couple of formulas you have provided, but can't seem to get those to work with the changes needed.

Thank you for any help,

danasliman

Hi @Paul Newcome I am new to SS and struggling with something that I think should be basic :( I am trying to calculate start and end time (hours\mins) based on duration. I have tried to read and understand some of your previous posts, but not clear if it applied to mine :(

I am trying to set the start time to 10pm and also not sure why end time is showing

PLanned Start date Time: =Start@row + ""

Planned End dat time: =[Planned Start Date time]@row + Duration@row

Appreciate your support! and this amazing forum!

Paul Newcome

@danasliman Try using the same method on the Finish column that you used on the Start column where you "added" two quotes to convert it into a text string.

danasliman

thanks @Paul Newcome

I have added to

Planned End dat time: =[Planned Start Date time]@row + Duration@row + ""

it still shows with random characters

Also, can not figure out how to set the

PLanned Start date Time: to be 10pm or any other time i choose to

What does it means when I "convert it into a text string" i watched a youTube smartsheet video and chose this method cause this is all I found… if there is a better way, would be happy to learn

Paul Newcome

@danasliman I meant without the duration.

=Finish@row + ""

danasliman

Thanks @Paul Newcome it works beautifully! i verified with the dependencies and different duration - it sums it up amazingly!

My final question is - would it be possible to set the start time to a specific hour or the day? with this Formula it sets it up for 12am, what if I would like to start at 10PM?

Paul Newcome

@danasliman The only thing I can think of right off would be inserting a placeholder task as a predecessor and setting the duration to 22 hours.

allison.worley

I had a post asking about converting time and after asking on here and getting help from Support we came up with a solution that works. My client would like to see the fraction of time to show in time HH:MM:SS. This should be a simple conversion, but I am getting the time and a decimal point for the seconds. Can someone help me with the ending string of this formula?

I know I am missing a string to capture the seconds, but I cannot seem to make it work correctly. Since this is a calculated duration of time AM/PM is not needed and HH can exceed 24.

=IF(INT((([BOT Run Time ref to the decimal]@row / 60) - INT([BOT Run Time ref to the decimal]@row / 60)) * 60) < 10, "0") + INT((([BOT Run Time ref to the decimal]@row / 60) - INT([BOT Run Time ref to the decimal]@row / 60)) * 60) + ":" + IF(([BOT Run Time ref to the decimal]@row - INT([BOT Run Time ref to the decimal]@row)) * 60 < 10, "0") + ([BOT Run Time ref to the decimal]@row - INT([BOT Run Time ref to the decimal]@row)) * 60