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

J. Craig Williams

Try this;

 

=IF(FIND(":", Hours2) > 0, VALUE(LEFT(Hours2, FIND(":", Hours2) - 1))) + (VALUE(RIGHT(Hours2, FIND(":", Hours2))) / 60)

 

If the colon ":" is not found, the LEFT formula causes the error.

If the colon is not found, the RIGHT function will win.

Blank cells will show as 0. I'm still trying to figure that one out.

 

Craig

 

 

blair myles

ok so once again that has worked brilliantly - thanks heaps.

 

now what i need to do is consolidate the data entered by individual staff members so that i can see totals accross a week and produce a report that shows stats for each staff member for a full week automatically

eg

name           total hours           billable hours      utilisation

jon                  40                           20                      50%

max                40                           40                     100%

 

how do you suggest i do this?

 

should i have a separate page for each staff member - seems extreme

 

given the staff are entering their own data via a web form, can it be done via parent child relationships?

 

not quite sure what approach to take here.

 

please help

 

Regards

 

Blair

Travis

I put something together for getting total hours worked. 

 

I added an Hour, Minute, and Total column.

 

The Hour column (brown) pulls hours out of the Hours column calculated time.

 

=VALUE(LEFT(Hours2, (FIND(":", Hours2) - 1)))

 

Minute (pink) pulls minutes.

 

=VALUE(RIGHT(Hours2, 2))

 

Hour parent cell (orange) sums hours.

 

=SUM(CHILDREN())

 

Minute parent cell (yellow) sums minutes and gets hours.

 

=SUM(CHILDREN()) / 60

 

Total (purple) adds orange and yellow.

 

=Hour1 + Minute1

 

Total (green) combines orange, yellow, and purple into a single cell. 

 

=SUM(CHILDREN(Hour1)) + SUM(CHILDREN(Minute1)) / 60

 

You can modify the SUM formulas to use SUMIF, so you want to sum hours if assigned to a specific person. 

 

Here's the sheet:

 

https://app.smartsheet.com/b/publish?EQBCT=e27c8cb197874ab8a05593937251c047

blair myles

ok so here is my sheet that will have data entered into it.

 

what i am trying to work out is when a user enters several days worth of timesheet entries using the webform and all of those entries come into the one sheet, how do i have them come in automatically under a parent row for each of their names and will the sheet automatically roll up the totals for each staff member or do i just leave the entries as random and create a new row called 'staff member total' for each staff member and use the sumif command to search the sheet and give me a total for each staff member. if i do this what will happen with each new entry when it comes in? will it be picked up by the formula or will the formula only relate to the entries that are there at the time when i write the formula? sorry for the many questions, this is the last step in my process and i am new to smartsheet. your help is greatly appreciated.

 

here is my sheet so you can see what i am doing.

 

https://app.smartsheet.com/b/publish?EQBCT=bda9314743b84c6c86fb34fd02e634f0

 

J. Craig Williams

Blair,

 

1. how do i have them come in automatically under a parent row?

- they won't without a third party or API solution. Not even then for Zapier or Azuqua, maybe (haven't tested this yet). WebForms enter at the bottom or top and stay there until moved manually.

 

2.  do i just leave the entries as random and create a new row called 'staff member total' for each staff member and use the sumif command to search the sheet and give me a total for each staff member?

That sounds like a great solution.

A report could be used to get the list, but the sumif from mainsheet is how to get the total.

 

3. if i do this what will happen with each new entry when it comes in? will it be picked up by the formula or will the formula only relate to the entries that are there at the time when i write the formula?

 

If you try to sumif from the same column (using something like sumif(column23:column42, blahblahblah) then the new WebForm row will start at 43 and your formula is broken. Instead put the formula in a different column and grab the whole column (sumif(column:column,blahblahblah)

 

Did I get them all?

Anything still missing?

 

Craig

 

 

 

blair myles

ok Craig, thanks for the above reply.

 

so my total billable hours formula is working really well with one exception. if the guys work 10 hours or more the formula doesnt work. not sure why. can you help please?

 

Regards

 

Blair

J. Craig Williams

Blair, 

 

Which formula did you end up using?

 

Craig

blair myles

this one...

 

=VALUE(LEFT([Total Hours]15, FIND(":", [Total Hours]15) - 1)) + (VALUE(RIGHT([Total Hours]15, FIND(":", [Total Hours]15))) / 60)

blair myles

https://app.smartsheet.com/b/publish?EQBCT=12c6a65b6b9a40a4bc8e6cc8d51e8e4b

 

here is the worksheet in question. if you change the total hours number to exceed 10:00 then you will see the totalhours column displays an error

blair myles

try this version. it is editable

blair myles

https://app.smartsheet.com/b/publish?EQBCT=6fe03de629e34b878ed34daf96de86c4

 

or this one even:)

 

 

J. Craig Williams

I'm not sure at the moment why we thought we needed to figure out where the ":" was for the RIGHT function.

LEFT makes sense. It could be 1:50 or 10:50, so relative to LEFT, the colon moves.

But not the right. Does it?

 

I changed this:

=VALUE(LEFT([Total Hours]8, FIND(":", [Total Hours]8) - 1)) + (VALUE(RIGHT([Total Hours]8, FIND(":", [Total Hours]8))) / 60)

 

to this

=VALUE(LEFT([Total Hours]8, FIND(":", [Total Hours]8) - 1)) + (VALUE(RIGHT([Total Hours]8, 2)) / 60)

 

Maybe I will remember or someone will remind me why we added that complication.

A long night of little sleep, travel, and beer make the reason escape me at the moment.

It works now (in your shared sheet)

 

Craig

 

 

 

 

 

blair myles

awesome thanks.

 

turns out i have the same problem with the billable hours columns.

 

any chance you could adjust this also ta?

 

Regards

 

Blair

J. Craig Williams

Done

blair myles

you are a Saint