IF Statement

[ragamuffin]

Hi,

I am creating a schedule for work (attached) in which I need to show all of the hours worked for the week in a single column. The shifts are not formated as time, but do have refrences to times that can be used in the clalculation. That may not make much sense, but you'll see when you open the attachment :) I think that I need an IF statement to do this calculation, but I am not sure how to accomplish this with multiple variables that can change from week to week. I would like to keep it to a formula, not a vba, if at all possible, as I am not good at vba at all and I will need to make several edits to the timesheet from week to week. Optimally, I would also like the code to be as such that I could simply copy the whole sheet into a new worksheet for the new week, change the dates and schedule, and then save (i.e. not have to redo any formulas). If anyone can help me out, I would be ever so thankful!!!

Thanks!

ragamuffin

[mdmackillop]

This could be greatly simplified if you use 24 hour notation as in
08:00-16:00
and a formula such as

=TIMEVALUE(RIGHT($C26,5))-TIMEVALUE(LEFT($C26,5))
should suffice. Is this possible?
Regarding the new week, I would suggest a blank hidden worksheet. Some simple code can make a copy of this visible for use.

[mdmackillop]

Here's the sheet with some partial formulae. I've used some named ranges (Data1, Data2 etc.), but these can be cell references if you prefer.

[rbrhodes]

ragamuffin,

I would use:


=(COUNTIF(C7:I7,"Open")+COUNTIF(C7:I7,"Close"))*8 (for full time)

and *5 for part time

As for the new Schedule simply copy the whole sheet.

Cheers,

dr

[ragamuffin]

mdmackillop and rbrhodes,

Thanks to both of you for all the help! I looked at both sheets and have a couple of questions. I was looking to use some sort of IF statement, which is featured on rbrhodes sheet. The statement is as follows: =(COUNTIF(C7:I7,"Open")+COUNTIF(C7:I7,"Close"))*8. I am not very keen with IF statements with multiple variables, so against my best efforts I cannot get this statment to include the "O/C" hours on a Sunday. I just want to make sure the hours are calculating correctly.

On the sheet provided by mdmackillop I am trying to learn something completely new. The hours seem to calculate very accurately, but I do not understand the Data feature works or how to expand it to include the other reps. Also, I do not mind using military time, but these reps will not understand that, so will it effect the code if I hide the rows or move the references somewhere else within the sheet?

Thanks again for both suggestions and the continued help.

Luke

[rbrhodes]

hi, r

Add a countif for Column I


=((COUNTIF(C7:I7,"Open")+COUNTIF(C7:I7,"Close"))*8)+(COUNTIF(I7,"O/C")*6.5)

What this does is count all instances of the word "Open" and counts all instances of the word "Close" and multiplies those by 8. Then if counts "O/C" and if found it's times 6.5. Then it adds it all up.

Cheers,

dr

[ragamuffin]

Thanks again for the help. The formula works great and I customized it a little more to include a Mid shift. Of course, once the spreadsheet was working the way I wanted it to the desired format that my company wants was changed. Now I have to actually have the times in there instead of set shifts like Open, Close, Mid, etc. So, of course, I need help with this, too. I do not know how to incorporate an IF statement that will calculate the hours scheduled for each day, subtract an hour for lunch or 30 minutes for lunch depending on if they are part time or full time, and calculate 0 hours for a day off. If this is possible, any help would be greatly appreciated! Thanks!

r

[rbrhodes]

Hi rm.

Here's an example using If statements. They're written to not display 0 values although that makes them a little longer. I prefer that myself but you could set excel to not display 0 values (and shorten the formulas) or not...

Two restrictions tho:

1) You cannot use the word "OFF" in the shift cells or it will error the formulas. Days off are simply left blank. If you must have the word "OFF" in the schedule we're into VBA (macros)

2) If you enter an in time without an out time the math will be cocked. I could write you an If statement to account for that but it's novel length (and these are long enough!) so again we're into VBA, I think.

Either way let me know how you're goin', mate

Cheers.

dr

[ragamuffin]

That works GREAT! My eyes are crossed now after studying your formulas! My hat's off to you. I have learned a lot by reading through your work one step at a time - I had no idea you could combine IF statements like that. Thank you. I think I understand everything that you did - it makes sense. The one thing that I cannot understand is how you are getting the total hours inluding lunches to calculate. If you could explain that one to me I would greatly appreciate it (I don't want to have to bud you over and over again in case I need to change something...:)

Thanks again!

rm

[rbrhodes]

Hi RM,

Anytime! Most of what I learned was through these forums so I'm glad to pass it forward. Post or PM me as you need to.

The two formulas work the same except the lunch one checks for "FT" or "PT" and then counts all non blank cells. Since every shift has two entries (Start/End) the part that says: Countif <> "" will count all the shifts.

Then it divides by 2 to get the number of shifts for full time. And if you look closely at the formula it subtracts that number from the hours, minus 1 hour for each FT shift worked.

For part time it counts the number, divides by 4 to get the number of shifts minus 1/2 hour for each shift worked.

Confused yet?

In other words, count every cell that isn't blank and if FT then divide by 2 to equal the number of days worked. then subtract result (1 * days worked) for every day worked

If PT then divide by 4 and subtract result (.5 for every day worked).

hth

dusty

[ragamuffin]

Thanks for the explanation! I get it now :) These are very well thought out formula with some cool tricks in there that I would have never have thought of. I removed the "OFF" sections from the drop downs and will count the days off with colored blanks (maybe using a conditional formatting). The sheet works beautifully. Thanks again for all of the help!

rm