Formulas for Calculating Time

JBolan

@Paul Newcome - I'm not looking for a decimal. I have to be able to find duration on cases that might go overnight. I believe that means I'll need military time vs a decimal, since a decimal is /100 vs /60. Military time should always include minutes in an HHmm format. Please advise?

Paul Newcome

Right. Even using a 24 hour time, you will not be able to calculate anything because Smartsheet does not currently have those capabilities. That is why you need to convert the minutes into a decimal so that you can see your start time as a number, your end time as a number, and subtract one from the other.

Once you get the duration number, there are solutions throughout this thread that will convert that number back into a time format.

Breanna

Hi @Paul Newcome,

Your threads have been so helpful for me, so have to start with thank you.

I am using a couple of your formulas, slightly modified to generate an end time based off of a start time and the project "Duration" column. I have converted the Duration from days to minutes using =(Duration@row * 7.5) * 60

Then have used the following:

End Column: =IF(VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Start Time]@row), 12), IF(CONTAINS("a", [Start Time]@row), -12)) + VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + (VALUE(MID([Start Time]@row, FIND(":", [Start Time]@row) + 1, 2)) / 60) + ([Covert Duration to Mins]@row / 60)

End Time: =MOD(INT(End@row), 24) + ":" + IF((End@row - INT(End@row)) * 60 < 10, "0") + (End@row - INT(End@row)) * 60 + IF(End@row >= 12, "pm", "am")

This is working perfectly for all end times with a half hour, but where the end time should be on the hour I am getting 60 in the minutes. Screen grab below, I have highlighted the 2 offending cells in yellow.

I am hoping you can help me out.

Paul Newcome

@Breanna If you click on the [End] column header and then show more decimals, is there anything there?

Breanna

@Paul Newcome, I have expanded the decimals nothing that looks out of place.

Paul Newcome

@Breanna Insert a temporary text/number column that we can use for trouble shooting and drop this in:

=(End@row - INT(End@row)) * 60

What does that output for that same set of rows as you have in your screenshot?

Breanna

Hi @Paul Newcome,

As requested I have added the =(End@row - INT(End@row)) * 60 Troubleshooting column. I have also added two additional troubleshooting columns, =MOD(INT(End@row), 24) and =MOD(INT(End@row), 12) for 12hr and 24hr times.

It potentially looks like the INT could be the problem here, so when we subtract it from the End@row value it equals 1 and not 0, which is why the value of 60 is pulling in. I am not sure how I would fix that.

Would that mean that there is an error in my [End] formula and we are just not seeing it in the decimal places? My brain is not really processing how the integer of 10 could be 9....

END: =IF(VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Start Time]@row), 12), IF(CONTAINS("a", [Start Time]@row), -12)) + VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + (VALUE(MID([Start Time]@row, FIND(":", [Start Time]@row) + 1, 2)) / 60) + ([Covert Duration to Mins]@row / 60)

Paul Newcome

@Breanna This is definitely an odd one. You may end up needing to reach out to support. It seems as if there is a bug on the back end. Your 10 is being treated as a 9 and your 11 as a 10, but it only happens to be in those two rows. I would hold off on reaching out to support just yet though. ENGAGE is next week, and they always have new features getting announced. A lot of us are keeping our fingers crossed for Smartsheet to have the ability to calculate time without needing all of these helper columns. Tuesday at 9:00am (Pacific) will be the keynote where they should be announcing new features. I don't suggest holding your breath, but maybe if we all cross our fingers extra hard we will hear something about time. 🤞🤞🤞🤞🤞🤞🤞🤞🤞🤞🤞🤞🤞🤞🤞🤞🤞🤞🤞🤞🤞🤞🤞🤞🤞🤞🤞

There is no issue with your End formula. It is outputting the appropriate value, but for some reason it is being stored on the back end as one lower than it should be.

This piece here:

=(End@row - INT(End@row)) * 60

reads as

=(10.00000 - INT(10.00000)) * 60

which is also

=(10.00000 - 10) * 60

Which in turn is the same as

=(0) * 60

Which should give us

0

And the MOD functions should be giving us 10 (not 9). So the display value in the End column is correct but the stored value is not.

Leibel S

@Paul Newcome

This was quietly added a short while ago:

https://help.smartsheet.com/function/time?frame=0&nav=1

Paul Newcome

@Leibel S I know. Haha. It was last Thursday, but since they hadn't officially announced it yet, I wasn't sure if we were supposed to be talking about it yet. They have been pretty hush hush about it, so I didn't want to steal their thunder. If they do announce it next week, I am going to immediately be flagging this thread as obsolete (with very mixed feelings). Hahahaha

Leibel S

I look forward to getting less notifications from here lol

It was actually in a marketing email or post somehere that they sent out (that they are working on it)

also, seems like they added a 'duration' type column (that you can add on its own - not necessarily via Gantt view), not sure if that is connected...

Paul Newcome

@Leibel S You can always unsubscribe from this thread without impacting notifications from other threads. I've thought about doing the same thing myself at times. 🤣

But yes. I saw where they said they were working on it, but they didn't provide a timeline. They have been working on it for years now though, so I never put much stock into "we're working on it" when it came to this one. I remember having a quick meet-up with the dev team maybe 4 or so years ago regarding it and some beta testing around this time last year.

@Breanna Since the cat is out of the bag now... Check out the TIME function and see if you can get that to work. If you are still struggling (new functions can have a bit of a learning curve to them), feel free to let me know how far you get, and I will be happy to help from there.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/395320#Comment_395320

I'm just now looking over this TIME function. It looks like a good start. I don't think it's going to make this thread obsolete though.

The function doesn't work for calculating hours across working days, unless your business goes 24 hours a day. We're still missing Automation for Record a Date/Time and Record a Time. There's still the underlying issue of system date/time values being recorded in UTC in the data layer and being converted to your regional setting at the application layer, which makes working with those date/time values wonky and unreliable.

Paul Newcome

@Jeff Reisman What I may end up doing (after the official announcement) is creating a new thread to delve into the TIME function that includes solutions to those challenges and just include a link to this thread for some of the outliers. A lot of this thread will be made obsolete just in the basic calculation of time, and working across days, etc. will be a bit different.

Jeff Reisman

@Paul Newcome

You can start with this one in that new thread :)

I don't think this one is working right:

Using this to find the difference between Created14 (7/11/23 9:44 AM) and Created1 (6/26/23 2:59 PM,) which should be 14 days, 18 hours, 45 minutes. Using the above formula, Smartsheet returns 15 days, 5 hours and 15 minutes. This is incorrect, because you only hit a full 15 days once you reach 2:59 PM on 7/11/23. This is evidenced by just doing Created14 - Created1, which yields the correct 14.78125. (Convert 18 hours 45 minutes to decimal, 18.75, and divide by 24, you get .78125.)

The problem is that this formula starts by just subtracting date from date, without considering if the time on the greater date is earlier or later than the time on the lesser date. With the end time being earlier than the start time, you need to subtract 1 from the result of DATEONLY(Created14) - DATEONLY(Created1), to get 14 whole days.

Then it's subtracting 9:44 AM from 14:59 PM, to get 5 hours 15 minutes. What you really need to do is count the number of remaining hours in the earlier date, and the number of hours that passed in the later date. To do this, subtract 14:59 PM from 24, which is 9:01, and add that to 9:44 AM, getting you 18 hours 45 minutes.