Formulas for Calculating Time

Sean Rebeck · 08/09/22

I have spent a lot of time creating a time formula that automatically converts time from one time zone to another. I think it would be very helpful to share with others. I have a smartsheet that I can share with you, please let me know the best way to go about it.

Thanks,

-Sean.

Jeff Zumaris · 08/09/22

@Paul Newcome there is an issue with Date/Time/Year https://app.smartsheet.com/b/publish?EQBCT=8b6ddddb66bb4b3db6fbbec745b46795&_ga=2.269049842.2095641566.1660064693-479407223.1643813565

If you take

Start Time: 08/08/22 - 12:17 PM

End Time: 08/09/22 - 10:24 AM

It want's to say this is 1 day 22 hours and 7 minutes. Should just be 22 hours 7 minutes?

Any update to the formula to catch date/times that are different days but less than 24 hours?

Paul Newcome · 08/09/22

@Sean Rebeck I believe there is already a time zone conversion solution tucked away in the thread here somewhere, but you are welcome to set up a sample sheet, publish it, then share the link here.

@Jeff Zumaris You are absolutely correct and there is even an example of that exact issue on the sample sheet. Let me work on that a little bit, and I will get back to you. In the meantime, there may be more recent solutions here in the thread that account for that as I specifically remember building that in to some of the solutions (apparently not this one though haha).

Paul Newcome · 08/09/22

@Jeff Zumaris I have corrected for that issue in the main sheet. Pleas ego ahead and revisit the published link to find the updated formula.

Thank you for bringing it to my attention! If you are going to be at ENGAGE this year, I'll owe you a drink!

Paul Newcome · 08/09/22

To everyone that visits this thread looking for a time based solution:

There have been functions and features released since some of these solutions were published, and I have learned a few other little tricks here and there as well. I cannot set a timeline, but here soon I am going to be working through these solutions and seeing if I can update them to create more efficiencies or make the overall solution easier to recreate. Please be patient, and of course always feel free to ask questions and make suggestions.

Thanks!

L_123 · 08/09/22

@Paul Newcome

Might be worth it to have a public sheet with the formulas at the head of the post, then the list can be right up top for everyone. been thinking about doing that for my date formula post as well. Forum for questions/suggestions, then public smartsheet for formulas.

Sean Rebeck · 08/09/22

@Paul Newcome I searched for a time zone calculation formula but was not able to find what I was looking for, it could just be buried in this thread somewhere. I agree with the comment that it would be helpful to have a sheet with all the solutions in one place and long term would be a really nice feature to add to smartsheets to manage automatically in the future. I will post my solution here for anyone to use if they find it helpful. Thanks for all your help Paul!

https://app.smartsheet.com/b/publish?EQBCT=17ab7b0549974fd49dfa34e96a910d99

Jeff Zumaris · 08/09/22

https://community.smartsheet.com/discussion/comment/339082#Comment_339082

I hope so I went to the last 2 that were in person but not sure i'll get funding this year for it. In a pinch I did this for a temporary solution for a sheet I built. (should be able to set timzone on a sheet maybe a system column like Timezone and its date only and all created dates are set to a timezone you pick from a dropdown.)

STime: =IF(LEN([Start Time]@row) = 7, VALUE(LEFT([Start Time]@row, 1)), IF(LEN([Start Time]@row) = 8, VALUE(LEFT([Start Time]@row, 2))))

ETime: =IF(LEN([End Time]@row) = 7, VALUE(LEFT([End Time]@row, 1)), IF(LEN([End Time]@row) = 8, VALUE(LEFT([End Time]@row, 2))))

SDAY: =RIGHT([Start Time]@row, 2)

EDAY: =RIGHT([End Time]@row, 2)

DayDecider: =IF(AND(ETime@row < STime@row, SDAY@row <> EDAY@row), "YES")

DaysACTUAL: =IF(AND(DayDecider@row = "Yes", [Updated Days]@row = 1), "", [Updated Days]@row)

I could condense this into less columns but was just working out a bandaid so they would stop asking me to fix times.

Paul Newcome · 08/09/22

@L_123 I thought about that, but I can't edit my original post. I was trying to keep a running list on each page when the comments got long enough to create another, but I fell behind on it. I may have to create a second thread with that in the original post and then also provide a link to this thread.

@Sean Rebeck It is listed in the original post at the top of Page 1.

@Jeff Zumaris I'm not sure I follow what issue exactly you are solving for?

Katie Lafferty · 09/14/22

I used this formula and it works brilliantly! It gives me total hours worked. Thank you Paul.

=IFERROR((((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + (VALUE(RIGHT([End Time]@row, 2)) / 60)) + (([End Date]@row - [Start Date]@row) * 24)) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + (VALUE(RIGHT([Start Time]@row, 2)) / 60))), "")

However, the time worked by staff can span 7 days a week around the clock (snow plow events). I need to now carve out (subtract) the Office Hours (Mon-Fri 7am-3pm) from formula above. I need to calculate the hours worked outside those Office Hours. A snow storm event (check in/out) can span over multiple days.

Paul Newcome · 09/15/22

@Katie Lafferty I'm not sure that particular solution is posted here in this thread. I do know how it would work, but I don't think I have any samples to share at the moment.

If you have a little time, I will try to throw something together and get it out here. It may be a little while as work is busy, and I will be at ENGAGE all of next week.

Katie Lafferty · 09/15/22

Thank you for getting back to me. I would be grateful for any help you can give. If you have time to help figure it out, that would be great. Preferably before the first snowflake hits :)

Sorry, I thought this was the best place to post.

Here is a quick view of my sheet using your formula in Total Hours

SnowOps.pdf

Paul Newcome · 09/27/22

@Katie Lafferty I want to confirm something real quick with you...

You say that you need to exclude Monday through Friday 7am - 3pm. What are the chances that an entry could span multiple weeks? Basically I need to know whether I would need to potentially exclude more than one Monday (for example), or if the max time would only be for a few days.

Katie Lafferty · 10/05/22

Hi Paul, I apologize I didn't see you question until now. It would never span multiple weeks. Maybe 2-3 days

Katie Lafferty · 10/06/22

In case a little more info is helpful... This formula will be used in a smartsheet form for municipal snow storm events that can last 1-3 days. The town gets reimbursed for emergency snow plowing by FEMA but we can't count the crew's regular office hours (7am-3pm). One tricky part is that staff could Check-In in the middle of office hours and/or Check-Out during or after office hours. Sometimes they never Check-In/Check-Out and will take cat-naps at the DPW facility.

Hope that explanation helps.

Formulas for Calculating Time

Comments

Categories