Formulas for Calculating Time

Paul Newcome · 06/27/24

@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 · 07/02/24

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 · 07/03/24

@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 · 07/03/24

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

Julie Becker · 07/16/24

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 · 07/17/24

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 · 07/24/24

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 · 08/02/24

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 · 08/05/24

@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 · 08/06/24

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 · 08/06/24

@danasliman I meant without the duration.

=Finish@row + ""

danasliman · 08/07/24

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 · 08/08/24

@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 · 10/03/24

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

Andrew Stills · 01/15/25

Created this formula to populate a non repeatable (unique) number from the Created (system date column)

Using 2 digit year + year day (with leading zero's) + hours and minutes after the decimal to signify partial day.

=VALUE(MID(Created@row, 7, 2) + IF(YEARDAY(Created@row) < 10, "00", IF(YEARDAY(Created@row) < 100, "0")) + (YEARDAY(Created@row) - IF(AND(VALUE(MID(Created@row, 10, FIND(":", Created@row) - 10)) >= 6, VALUE(MID(Created@row, 10, FIND(":", Created@row) - 10)) < 12, FIND("P", Created@row) > 0), 1))) + (((VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) + IF(VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) <> 12, IF(FIND("P", Created@row) > 0, 12), IF(FIND("A", Created@row) > 0, -12))) / 24) + ((VALUE(MID(Created@row, FIND(":", Created@row) + 1, 2))) / 60) / 24)

Screenshot 2025-01-14 at 10.52.07 PM.png

Limitations: 1) won't work if more than one row is created in the same minute (no seconds in the system date/time). 2) won't work at the turn of the century (only using the 2 digit year) 3) YEARDAY seems to be based of GMT so you'll have to adjust my offset ">=6" to your offset. You'll also have to determine if you want to account for Daylight savings time. I'm choosing not to… at present.

I'm using this to populate a unique number on a metadata sheet to avoid conflicts when a client has more than one project at a time and a project descriptor is manually input and could potentially be the same as well. Also found it useful when a data row is auto-copied to another sheet upon change and the older data row should be marked for deletion. The automation would have to occur more than once in a minute to throw an error. For my purposes this will work.

Let me know if any one finds this useful. Special thanks to @Paul Newcome for the hour (FIND) and to @allison.worley for the previous post giving me the idea to what she was doing and produce a decimal from a time instead.

Cheers

Formulas for Calculating Time

Comments

Categories