scheduling

[Guest]

I am fairly new to excel and formulas and I am attempting to write a formula
that when it takes in a retail shift for Sunday through Saturday, it takes
the amount of hours worked and totals it in a different cell. The main
problem that I see is that there are a lot of conditions that could be met.
Is there anything I can do to approach this?

Thanks,

Mike

 

[Guest]

Hi Mike:

You could try using additional columns for each day to make the calculations
easier to understand for each day and then add the cells together.

You could write a macro that handles the process.

But you really need to explain the conditions to get some specific help.

 

[Guest]

Martin:

Like during a full day, there could be an 8-5 shift, 9-6, 10-7, 11-8, 12-9,
and 1-10. Those could be all the 8 hour shifts, but then there could be 7
hour shifts, 6 hour, 5 hour and then only a 4 hour. So the spreadsheet would
have to recognize all the shifts and understand how many hours each shift was
and then try to total them. I am able to understand a basic IF(A2="8-5", 8,
0) statement. But being able to put in all the cases is proving more
difficult for me! Thanks for your help!

Mike

 

[Pete_UK]

Why don't you record the start-time and finish-time for each day, and
then just subtract one from the other to give the number of hours for
that day? That implies using 3 columns for each day.

Hope this helps.

Pete

 

[Guest]

Another way to do it is with data validation and vlookup.

You have the data validation list in say cell a1 which is a list and refers
to cells
f1:f6 = '8-5, '9-6, '10-7, '11-8, '12-9, '1-10 and in g1:g6 = 8,8,8,8,8,8

then in cell b2 =vlookup(a1,$f$1:$g$6,2,false)
to give the hours works.

But I think that Pete's suggestion is the easiest as you did not mention
about overtime if it needs to be considered.

 

[Guest]

yes that was great help, now how do I write an if statement where if the
shift is over 6.5 hours, than I need to subtract a full hour for the lunch?

Mike

 

[Guest]

If the above cells are formated as time:

=if(A1>=(6.5/24),-1/24,0)
or
=if(A1>=(6.5/24),A1-1/24,a1)

if they are numbers don't divide by 24

 

[Guest]

Alright,

Well I started to do it this way, and it ended up working fine....now all I
need to do is find out how to embed another if statement into it that
considers an input "off" and would put a 0 for the number of hours worked.
Any idea?

Thanks again,

Mike

 

[Guest]

sorry, forgot to show my example:

=IF(HOUR(D8-C8)>6, HOUR(D8-C8)-1, HOUR(D8-C8))

so if there was an hour specified, it is calculating it fine, but now what
if the cell says "off", I need it to tell me that there are 0(zero) hours for
that day...nesting if statement?

Thanks again!