using start and end time columns to calculate hours worked for a timesheet page

blair myles

i am trying to track staff hours by having them complete a web form to auto submit their timesheets. i need to be able capture start and end times for any given day so i can check how many hours they have worked. any suggestions?

J. Craig Williams

Blair,

 

Either they enter time as 8:15 or they enter it from a drop down list.

If entering like 8:15, you'll need to parse the time into hours.

 

If you want to have them enter from a drop down list, it gets a little easier (because you don't have to worry about error checking bad data entries).

 

You'll end up with two columns for the times, one for the date, one (not seen by the user of the WebForm) to do your calculations.

 

There's a couple good threads on the Community to help.

 

Hope that gets you started.

 

Craig

 

blair myles

Hi Craig, thanks for your reply. i like the drop down idea, that will work well. the bit i am stuck on is how i can calculate total hours worked in a day from the start and finish times. eg start 8.15 and finish 4.30. how do i change time format into number format to get me total hours worked of 8 hours and 15 minutes???

 

Regards

 

Blair

J. Craig Williams

Blair,

 

Check this thread out:

 

https://community.smartsheet.com/discussion/heres-fun-one

Since you like the drop down list idea, this fits.

Craig

 

Travis

Here is a non-dropdown option. The user would enter the start time and end time and the third column calculates time between. 

 

Feel free to adjust the StartTime and EndTime to test it!

 

 

 

Here's the formula:

 

=INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) + ":" + IF(((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) - INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) * 60) < 10, "0") + ((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) - INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) * 60)

J. Craig Williams

LOL

Or that.

 

Craig

Travis

Its an intimidating formula! But it is easy to use, just copy it into a text editor and use find and replace to add your own start and end date cell references to it, then paste it into your sheet. 

J. Craig Williams

You are going to scare people.

 

Craig

blair myles

thanks Travis, you are a legend!. this has worked perfectly. my next challenge however is how do i then subtract billable hours from total hours worked where total hours is created using your formula and produces a number like 8:30 being 8 hours and 30 minutes. billable hours also produces a number with the same format eg 6:15 being 6 hours and 15 minutes

 

 

blair myles

hey Craig, you are a legend too:)

 

just saying

J. Craig Williams

Blair,

 

re: comment to Travis:

This will get you the value of one of the hours

 

=VALUE(LEFT(Hours1, FIND(":", Hours1) - 1)) + (VALUE(RIGHT(Hours1, FIND(":", Hours1))) / 60)

 

Two of those together will give you the diff.

 

re: comment to me. You'll make me blush. But thanks.

 

Soapbox:

Your request to Travis is one of the reasons why I would normallly have a solution using two or more column instead of the that beautiful and horrible formula of Travis. 

No matter how much we think we understand the requirements, someone always wants a bit of something that isn't in the original spec.

Using the two or more column solution, the answer would have been (probably)

 

=[Total Hours raw]1 - [BIllable Hours raw]1

 

Another reason is that there are 2762 chararacters (incuding white space) in that formula. And I know I would have mistyped one of them along the way. I'm pretty sure Travis did not build it all at once, likely used two or more columns to figure out the pieces and then combined them at the end. For me, that is extra work (the combining) for likely extra work later (figuring out what went wrong or figuring out how it works)

 

Once I can pick and choose which columns are hidden/unhidden quicker and more configurably (feature request), I'll be even better off.

 

Steps carefully off the soapbox and goes back into his cave.

 

Craig

 

Travis

Thanks Blair, but I cannot take credit for this formula! One of our consultants, Ajay, built it. 

 

We do have plans to add time formulas to Smartsheet, so we wont have to use these "beautiful and horrible" formulas. I dont have an eta for this but it is something we are actively working on. 

blair myles

ok Craig, have used your value formula which works great.

 

only thing now is how do i get it it ignore a column that has no value in it as my total only works if all three of my billable hours columns have values in them which is not always the case

 

blair myles

all good travis, i am happy to use your formula for now

 

blair myles

bugger, hey craig scrap that last question. my bad as i had some crap data in one of the fields that was screwing it up. cheers

blair myles

ok, so i have used the above =value formula which works really well...except where there is no value in the field that it refers to. in which case it gives the following error ... # missing or invalid parameter.

 

what do i need to add to the formula to prevent this from occuring when the field is blank?