Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

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

blair myles
edited 12/09/19 in Archived 2016 Posts

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?

«1345

Comments

  • J. Craig Williams
    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

     

  • 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
    J. Craig Williams ✭✭✭✭✭✭
    edited 03/02/16

    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
    Travis Employee
    edited 03/02/16

    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
    J. Craig Williams ✭✭✭✭✭✭

    LOL

    Or that.

     

    Craig

  • Travis
    Travis Employee
    edited 03/02/16

    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
    J. Craig Williams ✭✭✭✭✭✭

    You are going to scare people. :)

     

    Craig

  • 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

     

     

  • hey Craig, you are a legend too:)

     

    just saying

  • J. Craig Williams
    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
    Travis Employee

    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. 

  • 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

     

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

     

  • blair myles
    edited 03/03/16

    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

  • 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?

This discussion has been closed.