PDA

View Full Version : more excel help...uhg.



WendyI
September 2nd, 2016, 01:31 PM
Ok....i need more excel help.

In my file I have a sheet for every month. In each month I have each day outlined:

1-Aug
2-Aug
3-Aug

etc.

If each work day is comprised of 8 hours (including a one hour lunch) how do I total the number of total hours in a given month that a person is required to work? Does anyone know that formula? The dates are in column C (starting at row 4) but there are blank rows in between. Here is an example of my sheet

134683

So 8 hour days and in the month 18 work days in the month. How do I get Excel to tally that without me having to do it manually for every sheet I create?

Thanks for any help!!

WendyI
September 2nd, 2016, 01:32 PM
This is the formula my boss tried but the result came up as 0:00

=COUNT(C4:C7+C12:C15+C19:C23+C27:C31+C35:C38)*7

Cool Breeze Quilter
September 2nd, 2016, 02:09 PM
I'm not sure if I'm following your question correctly or not. Are you trying to add up the columns that say total working hours? (I'm not sure from looking what column # that is.) If so then I would take the 25.0 hours that you have from week one and continue to do the same for the end of each week. At the bottom I would have a column to add those weekly totals. If you want to take it a step further to verify accuracy then you could add each one of those columns (excluding the total column) to make sure that one comes out the same at the end of the month.

WendyI
September 2nd, 2016, 02:23 PM
No. I have that figured out. What i need is the baseline hours for the month. Everyone must work a set number of hours per month. A given day has 7 hours of work time plus an unpaid lunch. That's fine. But we have flex time and people need to know what the base hours for the month are.

So if the regular work month has 31 days, and each work day spans 8 hours, that is equal to 248 hours less one hour of unpaid lunch per day. So I guess really 31 x 7 hours.

I want excel to calculate the hours for each month as I update each year of months. Does that make sense?

Lori63
September 2nd, 2016, 02:37 PM
Oh Wendy I wish I could help you, but I've never been able to figure Excel out. Hopefully someone will come along who can.

Mimi_Howard
September 2nd, 2016, 03:41 PM
No. I have that figured out. What i need is the baseline hours for the month. Everyone must work a set number of hours per month. A given day has 7 hours of work time plus an unpaid lunch. That's fine. But we have flex time and people need to know what the base hours for the month are.

So if the regular work month has 31 days, and each work day spans 8 hours, that is equal to 248 hours less one hour of unpaid lunch per day. So I guess really 31 x 7 hours.

I want excel to calculate the hours for each month as I update each year of months. Does that make sense?

If everyday is a "work day"... then use one block where you would type in the number of days in that month. In the next block would be the forumula... Ex.. A1 (31) in A2 you would type =A1*7 So everytime you manually change the number of days in a month... the next block would automatically do the multiplication each time too. Does this make sense???

Mimi_Howard
September 2nd, 2016, 03:45 PM
I should have added... once you type in the "equal" sign, then put your cursor on the A1 cell and a blue A1 appears in your A2 cell, then add *7...

Cool Breeze Quilter
September 2nd, 2016, 04:14 PM
If I'm following this right then what Mimi Howard says makes sense. This way it will automatically give you a running total.

Mimi_Howard
September 2nd, 2016, 04:25 PM
I was doing the math formula for just the total number of hours for that month... do you need to also take that number and then use it to deduct the hours as they are worked by the person... ? Don't want to make it more complicated than needed for sure..

WendyI
September 2nd, 2016, 05:21 PM
Thanks! I figured out an easier way! LOL! sometimes you just have to plow in and it works itself out! thanks again everyone xooxoxxoxo

Mimi_Howard
September 2nd, 2016, 05:23 PM
You are very welcome!

WendyI
September 7th, 2016, 11:25 AM
Ok...dang...my way isn't going to work and I'm not sure I understand what you mean. Being a visual learner STINKS!! I have such a hard time with deciphering things when it comes to numbers! I really should get that tested.

SO...if my first cell that has an Arrive time is D4 and the next cell has the Depart time (E4) minus lunch (F4) to get total working hours for the day G4). THEN the next day is the same on the next row (D5, E5, F5, G5) Then below each week after skipping sat and sun, I have a sub total for the week which calculates at the bottom of the sheet in TOTALS for hours worked in the week (G39).

Then I have Previous Carry Forward Hours (G40) which has to come from the previous month, Required Hours This Month (G41) which I can't figure out how to calculate either, then New Carry Forward Hours (G42) which, once calculated, needs to carry forward to the next months sheet into Previous Carry Forward Hours (G41).

Is this even doable?? I tried to attach the actual document but it's too large.

PatinValrico
September 7th, 2016, 11:35 AM
I always hated word problems in school. :icon_beuj:

WendyI
September 7th, 2016, 11:38 AM
I always hated word problems in school. :icon_beuj:

Right?!?! uhg.

Caroline T.
September 7th, 2016, 12:41 PM
This is the formula my boss tried but the result came up as 0:00

=COUNT(C4:C7+C12:C15+C19:C23+C27:C31+C35:C38)*7

Try this: =(COUNT(C4:C7+C12:C15+C19:C23+C27:C31+C35:C38))*7

(note the extra "(" before the word count & another ")" after the last range of cells before the "*7"

WendyI
September 7th, 2016, 12:51 PM
Try this: =(COUNT(C4:C7+C12:C15+C19:C23+C27:C31+C35:C38))*7

(note the extra "(" before the word count & another ")" after the last range of cells before the "*7"

I get a zero :( Then I tried to change the cell to "Time" but that didn't work either. BUT at least I didn't get an error! LOL! Maybe I'm using the wrong cells?!

So somehow tell it that when it sees "1-Jun" that should be equal to 7 hours. Is this possible? Then SUM that in the Required Hours This Month field?

Caroline T.
September 7th, 2016, 01:00 PM
If you want to email the worksheet again, I can take another look for you.

WendyI
September 7th, 2016, 01:04 PM
If you want to email the worksheet again, I can take another look for you.

You're awesome! Thanks!

WendyI
September 7th, 2016, 01:10 PM
Oh! One more thing...

I may be adding another row that indicates "Special Carry Forward Hours". The rules are that people can carry forward a maximum of one business day in flex time hours which is equal to 7 hours. From time to time there may be exceptions made where someone may be permitted to carry over a few extra hours. So that will also have to be calculated in also...I'm just waiting to be informed if exceptions will be permitted or not.