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

J. Craig Williams

Blair,

 

Not yet.

I think one needs to be:

1. Catholic (didn't take)

2. Pious (debatable)

3. Perform 2 miracles (I've only have 1 verified and that is a long story)

4. Dead (last I checked, still alive)

 

I appreciate the sentiment though. 

Glad I could help. 

 

Craig

 

Craig 

Amye

How would one edit that mammoth formula Travis has supplied. What exactly am I looking for to change my 'start and end' references??? I understand teh concept but have tried a few amendments to no avail.

 

I have Task in column 1, start time in column 2 and end time in column 3 with this 'total' populating into column 4.

 

Appreciate any clarification for a very new newbie!

J. Craig Williams

Amye,

 

I wouldn't. That is one of the reasons why I would do it differently.

But if I would, here's what I would do:

 

1. Copy the formula to a text editor (of your choice - it needs to be able to find & replace.

2. Find and replace "EndTime" with the name of your ending date column.

Use [] around your column name. 

3. Do the same for StartTime.

 

Copy the entire formula back into the same cell you pulled it from (if you pulled it from Smartsheet) or row 1 if you didn't (the formula given reference row 1)

 

That should work. Let me know if it doesn't.

Craig

 

Amye

Champion effort as always Craig! Thanks! For now I have it working, no doubt I will return and re-group when the time comes! 

 

Thanks!

Mike Thomas

When I copy the formula into my sheet, I get an $UNPARSEABLE error. My labels are StartTime1 and EndTime1.  What am I doing wrong?

J. Craig Williams

StartTime and EndTime are the column names, with row #1?

Or StartTime1 and EndTime1?

If the second, then you need to use [StartTime1] so the cell reference is [StartTime1]1 not StartTime11.

 

If the first, then I think that was the original formula's names already.

So it must be something else.

 

If you share the sheet with me, I'll fix it.

jcwill23@gmail.com

Craig

 

 

 

blair myles

hi there, me again!

 

aside from trying to create the worlds longest thread - sorry about that...

 

i have now go my timesheet auto collection going perfectly for which i am very grateful for everyones help.

 

so each staff member uses a web form to auto complete their daily timesheet. i get 5 timesheets from each staff member and they all end up in the one sheet with a total # of hours worked for each staff men]mber for each day of the week that they are working.

 

next question is, is there a way that i can create a report that totals the daily hours worked for each staff member to save me exporting the report in excel and summing the 5 individual totals to get my weekly total.

 

i thought maybe i could use a sum of children type function but not sure how to get the auto entries to assemble under a parent heading without manual intervention.

 

do i need to link entries into a new sheet for each staff member and use some type of function to total the entries in the new sheet and then produce a report to bring all of those sheets back together - seems complicated.

 

please help.

 

Regards

 

Blair

Dan Prascher

That all is awesome. Thanks for the help Travis and Craig.

 

I'm having troubles taking it one step further: I want to do what Travis has coded, but I want to be able to do it over multiple days. This is to track engine downtime: an engine could be down for 37 hours and 24 mins or 15 mins. How can I enhance the code that Travis or Craig put forth to accomplish this?

 

Thanks in Advance,

 

Dan

Carla Lopez

Hi,

 

I'm hoping I can get help to solve this problem.  Currently putting in start and end times works for one row however not for another since the start time is 12am.  It's actually counting it as negative hours.  Help?

J. Craig Williams

@Carla & @Dan -

 

Can you post the formulas you are currently using?

Dan, for sure you will need two date columns, not one.

 

Craig

J. Craig Williams

Dan

 

Are you using a dropdown for the time columns?

You are probably going to want it to work if it starts on 12/31/16 and ends on 1/1/17 too, right? (I'll be back)

 

Craig

 

Dan Prascher

Hi Craig,

 

I'm using Travis' code from the discussion, "using start and end time columns to calculate hours worked for a timesheet page". That code is below. I can't figure out how to add time to the result if it is 1 or more days later. Moreover, I'm having trouble understanding the code below to modify it to create the result if the the start time is after the end time. Example:

 

StartDate = 1/1/17, StartTime = 3:00 pm,

EndDate = 1/3/17, EndTime=  8:00am.

 

Here is the code:

=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).

Dan Prascher

Craig,

 

I'm not using dropdowns and, you're right, I would want it to span year's end.

 

Dan

 

J. Craig Williams

I also stand by my original comments here:

 

https://community.smartsheet.com/discussion/using-start-and-end-time-columns-calculate-hours-worked-timesheet-page#7404

 

that say one column is a mistake when anyone wants to add a new requirement.

 

Craig

 

J. Craig Williams

Without dropdown, you risk having this "3:00pm" and "3:00 pm" which is an added complexity.

 

Craig