Excel ... need some "basic" help

[P5ykoO]

Hello there,

I am not entirely sure this is the correct sub-section of the board to ask for help in, however, it's what I would assume this kind of request would be filed under considering the options at my disposal.

 

Great news everyone (Futurama ref.) I have a question for you ^^

I'm a construction worker, recently back to work and in charge of a team of 23 people ... I am currently trying to make my job easier by actually bothering to give a damn and get my shit on spreadsheets, calculating hours and stuff automatically, and overall just making it easier for me to provide my boss with the correct amount of time someone has worked so they can get the correct paycheck ... bla bla.

 

I am however a massive noob with excel (in my case the google docs spreadsheet, but same principals should apply) as I have never actually used excel in my life up until now.

And googling has brought up some random stuff that didn't really help me.

 

 

So I'm looking for a few formulas to apply to this form:

 

 

 

 

What I want to do:

1. Have (7C+7D)+(10C+10D)+(13C+13D)+(16C+16D)+(19C+19D)  add up into the cell 23C. Implying they are not noted in hours but in minutes then divided by 60 for the number of hours.

2. Have 22C = all the above minus 26h (so the number of minutes divided by 60) so I can see exactly how much over time they have done.

3. Have 21C = all the above equal 26h minus the amount found to show any amount of late time once 26h have been reached.

 

 

Yeah, I'm really sorry, but I would love to learn more about this, however I lack time to learn more ... waking up at 4:40am in the morning, going to work, and getting home at 9pm / 10pm at night.

If anyone has the heart, I would love to have an explanation of how it works, instead of just copy pasting a formula ... so I can learn in the process of doing it.

Any help would be massively appreciated <3

 

 

PS: Sorry, I'm French :'(

[P5ykoO]

Little explanation, I am currently doing all this with paperwork ... meaning being able to do it at home when I get back from work on my computer, just makes it easier for me.

[minibois]

1. =SUM((C7+D7)+(C10+D10)+(C13+D13)+(C16+D16)+(C19+D19) / 60)

(I think this is what you want? Count all those cells up, which is in minutes, than divide by 60 to get it in hours?

 

2. =SUM(C23 - 26)

(I'm not just relying on the info of cell C23, which is filled with the formula above and just extracing 26 from it, leaving the amount of hours more than 26)

 

3. Can you explain this one again? I don't really understand what you want. Do you want the time worked minus the time they have been late?

 

You can paste these formulas in the cells you want the answer to be in.

[P5ykoO]

Yeah, the third one, is basically, counting the hours (in minutes then dividing by 60 for hours) then dividing 26 hours (so 1560 minutes) by the amount found to know how many late hours.

This allows me to account for overtime on other days compensating for the weekly hour quota of 26.

 

I am working with people who are off the streets / out of jail / youth with problems ... giving them a second chance and new start in society, and their contracts are 26 hour ones.

Reinsertion contracts basically.

 

 

PS:

Thank you so much already <3

 

 

Edit:

 

 =SUM((C7+D7)+(C10+D10)+(C13+D13)+(C16+D16)+(C19+D19) / 60)

 

If I white 120 in one and 120 in the other (next to it) it gives "240" instead of "4"

So, it's not dividing by 60, just adding the cells ... any idea ?

[P5ykoO]

I am trying to enter time values now, instead of trying to divide and stuff ...

 

But say they start work at 8, so 08:00:00 and finish their morning at 12, so 12:00:00 ...

In the cell for the result I have " =SUM(C5-C6) " but it adds up to 20:00:00 instead of 04:00:00.

 

What am I doing wrong ?

 

 

Edit, my bad I'm just dumb >.<

That problem is solved

 

[minibois]

29 minutes ago, P5ykoO said:

 

9 minutes ago, P5ykoO said:

 

Sorry, I didn't really take in account that you would do it like this, the =SUM command is just counting up stuff. It works for simple integers, but not for hours minute second formats you use.

Personally I have never done this, but this should help:

http://webapps.stackexchange.com/questions/22657/how-do-i-calculate-a-time-delta-in-my-google-spreadsheets

It's someone asking a question about time differences and how to calculate it.

 

So with the third question you basically want to make the hours they are late a 'pool' from which the spreadsheet will extract numbers once they have done work? Hmm, I don't know really how to do that. That's a bit too advanced for me.

[P5ykoO]

Yeah, basically, to not fuck them over for being late (they already only have 26h of work a week ... doesn't make great paychecks) I allow them to work overtime with me, to "catch up" the late time ... so yeah.

And tracking that time on paper, can sometimes be a little complicated for me since I have 23 people, and don't always calculate every time for everyone, meaning a spreadsheet truly speeds up an facilitates my work ^^

[minibois]

11 minutes ago, P5ykoO said:

Yeah, basically, to not fuck them over for being late (they already only have 26h of work a week ... doesn't make great paychecks) I allow them to work overtime with me, to "catch up" the late time ... so yeah.

And tracking that time on paper, can sometimes be a little complicated for me since I have 23 people, and don't always calculate every time for everyone, meaning a spreadsheet truly speeds up an facilitates my work ^^

Maybe you can make the spreadsheet rely on an hour 'pool' anyways. Basically, you have 26 hours in a cell written down, everytime the work hours someone did for a day, it extracts the 26 by the hours done on that day. Lets say they come in 5 minutes late, they just have to write down that they came into work at let's say 9:05 and left at 17:00. The spreadsheet automatically registers that those hours mean a work day of 7 hours and 55 minutes and it extracts the 26 by that. Workers will every week know how much hours of work they have left and know "Okay it's the last day, I have 4 hours  and 35 minutes left of weekly work to do" and they know how long to work for that day. Maybe such a system works.

 

The way you could achieve this is, you have a cell for that 26 hours - the work and you could do it something like "26 - All the work amount cells"

[P5ykoO]

Oh yeah, that could actually work out, so basically it would be a "cell 07:00:00" - "cell total hours".

(Since they can only work 7h a day) but have Thursday afternoon and Friday all day to catch up lost hours

 

Ok yeah thanks again for your help ^^