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

Options
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 ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Options

    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
    Options

    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 ✭✭✭✭✭✭
    Options

    LOL

    Or that.

     

    Craig

  • Travis
    Travis Employee
    edited 03/02/16
    Options

    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 ✭✭✭✭✭✭
    Options

    You are going to scare people. :)

     

    Craig

  • blair myles
    Options

    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
    Options

    hey Craig, you are a legend too:)

     

    just saying

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Options

    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
    Options

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

     

  • blair myles
    edited 03/03/16
    Options

    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
    Options

    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.