Calculate time & subtract business hours from total

Katie Lafferty

I've setup a sheet that calculates time worked by staff (using a great post by Paul Newcome) & it works perfectly. However, our snowplow staff can work for days around the clock and I have to exclude all regular business hours (Mon-Fri 7:00-15:00) from that total.

Genevieve P.

Hi @Katie Lafferty

How are your times being recorded in your sheet for your "outside business hours" rows? It sounds like the easiest thing to do would be to ensure that any time outside of the regular hours would be a separate row. Ex. if the staff member worked some hours before the "business hours", those would be on one row, and then three hours within the "business hours", these would be recorded in a separate row.

That way all you need to do is have a checkbox formula that looks at the date and identifies if it's M - Fr, and if it is, checks the see if the VALUE of the two characters on the LEFT side of the Start Time is less than 7, or greater than 15, then we know it's outside of the regular hours.

Then you can quickly Filter by that checkbox column to only see the applicable rows, and even SUM them in a Report if that's helpful.

Let me know if your process is set up this way! If not, it would be helpful to see screen captures, but please block out sensitive data.

Cheers,

Genevieve

Katie Lafferty

Thank you for your input Genevieve :)

I entered the formulas by Paul Newcome (the link is in my initial post). They're a little complicated for me to know how to insert your idea. Should I start from scratch using different formulas?

If too involved to answer in a community post, I understand.

Genevieve P.

Hi @Katie Lafferty

The formulas themselves should be fine! Paul is a wizard when it comes to Time formulas so I would definitely recommend sticking with his equations.

The question is more about your process: when do you create a new row, and could you create two separate rows if someone is recording a time outside or inside the timeframe you're looking to exclude?

Katie Lafferty

Not sure how to explain it other than with an example...

I would use the form as a template for each snowstorm event, a blank one being used for each event.

The goal is to determine total cost of a snow event totaling staff rate/time & vehicle rate/time, minus the regular office hours during the event.

I thought about subtracting out the "office hours" by adding a deduct column at the end to manually enter the known office hours after each snowstorm event, but each staff member does not necessarily work same hours so that "deduct" may not be the same for all.

Example...If snowstorm event lasts from Thursday 2/24 at 10pm (around-the-clock) until Saturday 2/27 at 7:00pm, how can I automatically subtract out the known office hours at the end for each staff member

Office hours are Mon-Friday 7:00am-3:00pm

Katie Lafferty

Hello Genevieve, I have a meeting today with SS Customer Success Team to look at this project overall and see if we should setup a Launch Package to help get this done. This is one issue of many and looks like a big exercise in formulas. Hopefully we can work our way through it.

Thank you for you input :)

Genevieve P.

Hi @Katie Lafferty

Thank you for the update, and my apologies for the delay!

The easiest way I could think of setting this up would be if you were recording each day and time instance individually... ex. one row would be the first day outside working hours, the next row would be that same day but within the working hours, next row is the next day outside working hours, etc.

However it looks like you would be adding one row that spans over multiple dates and times, which then would require some potentially complex formulas, you're correct. The formula would need to identify the Working Days between your two dates, but then also be able to identify partial times (meaning you couldn't simply multiply these days by 8 to find 8 hours, in case your start time was later than the working hours or end time was earlier).

Having a meeting with Customer Success sounds like a good idea to ensure this sheet is being set up in the best way from the beginning. It would also be helpful if you could provide your feedback around how you're using Time in Smartsheet to the Product team so they can hear about your use-case and feature request!

Thanks,

Genevieve

Katie Lafferty

It is multiple sheets with a Dashboard & reports including staff time, vehicles used in snow storm, material quantity & costs and has grown into something bigger than originally thought. I have built it out pretty far and just need help getting it over the finish line. I have high hopes that it will be a great asset to us when completed. Thanks again :)