NEED help with Basic time sheet and hours worked calculation

**ebyron** · 07-22-2012, 10:18 PM

I am not an accountant or office worker. i am a union stage hand working at primarily rock and roll concerts. i have only used excel in the past to make simple lists and tables.

i have a new job as crew chief in a small theatre. i want to use excel to calculate hours worked for my guys.

there are some unique challenges involved including subtracting meal breaks, overtime hours after 8 hours in a day and some guys only working a load in in the morning and a load out at night. plus lie times sometimes span two calendar days

it is not pressing and i have no time schedule to meet with this project. i would just like to not have to calculate suing my fingers (wink)

is there anyone out there that is willing to help me figure out the formulas for my time sheet?

thanks
eliot

**npamcpp** · 07-23-2012, 02:42 AM

Yes, there are plenty of people here who will be able to help you. It would be good to know more about your data and your spreadsheet setup, because formulas depend on cell references, data type, etc. Results can be formatted in different ways or converted from date/time to other numeric data.

Your best bet is probably to post a sample file here. Use dummy data instead of real names. Explain what the input is, what the logic is (in simple words) and what you would like to see as a result.

We can take it one step at a time, and you will be in for a learning curve. It's fun!!

**dogberry** · 07-23-2012, 04:10 AM

Yes a template of what you require would be excellent. However to get you started a basic formula would be =IF(FinishTime>StartTime, FinishTime-StartTime, FinishTime + 1 -StartTime). FinishTime and StartTime would be cell references for the information, the above formula covers both the 9am to 5pm and the 10pm to 4am the next day senarios.

For total hours worked change column to custom number format select hh:mm and alter it to [h]:mm otherwise your total hours will not add up properly.

Hope that helps to get you started.

**ebyron** · 07-23-2012, 11:26 AM

thanks for the responses... i think npamcpp might be getting my speed more then the "greek to me" response from Dogberry

first.... how do i post my single page timesheet for you to look at?

**dogberry** · 07-23-2012, 01:21 PM

Hi

Sorry didnt want to confuse you it will make perfect sense to you later (honest

). To upload your file click on reply to thread below the text box you will find manage attachments click on that and follow the prompts to upload. I find copying your file to your desktop first easier, that way you know exactly where it is and you dont upload the wrong file by mistake.

**Toonies** · 07-23-2012, 01:40 PM

hi how many people appear on the Timesheet, I have one that you could use.

**ebyron** · 07-23-2012, 01:45 PM

so here is my sample time sheet
we are union stagehands working a concert at a theatre
some do the load in, show, and load out
some just come for the load in and the load out

Eliot (me) and tom
arrive at 8am and stay until 2am When the job is done
we take a meal from 1-2 and from 7-8 (unpaid)
we get 8 regular hours and anything above that is time and a half

**** and harry arrive for the load in at 8am
they take the meal break with us from 1-2
then go home at 5pm and return for the load out at 11pm
they are done with us at 2am but they get 4 hours at time and a half (minimum) for returning for the load out (so i log 4 hours @ 1.5x )

i do not need to calculate their pay with their rates... just the hours worked.

does this make sense so far?

Excel Forum Test 1.xlsx