Staffed Time, Break Time, Expected time- formula puzzle

[friarpop]

Hello all, thank you for your curiosity and help. I am a beginner at excel and need help creating a formula at work. Here is the what im up against:

Column B (staffed Time), Format 8 hours = 8:00:00AM=formated 8:00:00

Column E (break time), formated(15 or 30 minutes depending on shift)=12:30:00am= 0:30:00

Column M (expected time) (time on phones if perfect +feeding unused breaktime back into expected time (eg 8 hours shift - 30 break = 7.5hours = 7:30:00am = 7:30:00 or 8:00:00-0:30:00. ( if only used 10 minutes of break of 30 total put 20 minutes back into expected time so that 8:00-0:30:00+0:20:00 = 7:50:00. & also if possible add in any overages from staffed time, If they log in 5 minutes early make that staffed time 7:35:00 assuming 30minutes of break, but no underages if they are late tough. Tough huh? I was using cells to reference different employees shifts eg 8 hours & 30 minute breaks or 4 hours & 15 minute breaks. I have different books for each employee & column a is just dates of each day of the month. Help me if you can. Ask me questions. Would like to know by tomorrow if possible.

Just running windows xp & excel 2003 sp3. I came up with a partial formula =IF(E3>=$M$40, $M$41, ($M$41)+($M$40-E3)) but it didnt work with the staffed time overages & also filled in days that were blank as if they worked 8 hours. I just deleted them but if we can get it to not fill them great.

 

[friarpop]

Also, is it possible to get it to recognize other shifts, for instince if a person works 4 hours on one day & 8 on another. This one seems to fill in the M41 all the time. I can just enter those days manually but just wanted to see if it was possible. I would probably need another reference cell for the other shift they could work whether it be 4, 6, 8 or 10 hours. Let me know what could be done if it could be. Sorry not trying to be difficult, also learning.

 

[crook_101]

Ok - so I didn't go to bed......

To get what you need you will have to do the following....in cell

L40 enter 04:00:00
L41 enter 08:00:00
M40 enter 00:15:00
M41 enter 00:30:00

All these cells formatted as time

In M3 enter the formula:

=IF(B3="","Day Off",IF(B3<L40,B3,IF(B3<L41,(B3-M40),(B3-M41))))

and it will do all you requested

This time, good night!!

Any problems, post back
Ian,

 

[friarpop]

Thank you kind sir. I would like to send you a small token of my appreciation in return for your time if you feel comfortable with it, how can i get your email so I can send you something. Also it doesnt appear that you posted the formula or all of it.

 

[crook_101]

Morning,

Apologies for the lack of the full formula - told you I was tired

=IF(B3="","Day Off",IF(B3<L40,B3,IF(B3<L41,(B3-M40),(B3-M41))))

pasted into M3 should do the job for you if you enter the data as per previous post.

Any problems, please repost

Really no need for anything to be sent over for my time etc, enjoy the challenge If it is interesting though please feel free to add "@yahoo.com" to my user name and send it through - hate posting full email addys as spam goes through the roof from scrapers on some sites.

HTH
Ian,

 

[friarpop]

Ok, I'm still having a bit of trouble with this. What I want to have my expected time (column M) autocalculate. Its hard because of various shifts. If they work less than 4 hours they are expect to have 4 hours. If they work 4 between 4-8 they are expect to have their time minus 15 minute break (eg 5 hours - 15 = 4:45:00). If they work 8 or more they get 30 minutes of break (8:00:00-0:30:00=7:30:00) I can type in an approximate expected time however if someone is 5 minutes late a reference cell for 8 hours would only give them 15 minutes for under that. I want them to still get the 30. Also I want any break time they dont use to go back into expected time. say if they use 10 of 15 minutes then they would have 5 more minutes added back on to expected time. This is really confusing. Just looking for ideas/help on this complex request.

 

[crook_101]

Hi again

Dug through archives and found the full formula!!

Try the below if this is what you want:

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M
2	staffed time	aux time		break								expected
3	09:26:24											08:56:24
4
5
6
7
8	8 hours			30
9	4 to 8			15
10				0
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40											04:00:00	00:15:00
41											08:00:00	00:30:00
Sheet1

Ian

 

[crook_101]

Don't forget that you need to enter the data in the 4 cells L40 - M41

 

[friarpop]

Crook, thanks again, this is working i just cant get it to get any unused breaktime to feed back into the expected time in addition to this. Other than that this is working great!

 

[crook_101]

Hi,

Right I think I have this sorted

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	Z
2	staffed time	aux time		break								expected	Early Break Return
3	09:26:24			00:10:00								09:16:24	00:20:00
Sheet1

You will need to add a further cell for the calculation- i have chosen column Z as I cannot see how far your sheet goes accross - you can hide this column if you wish. You will have to format both this and column E as time - hh/mm/ss if it does not work striaght away.

WHat we have is a counter now that counts, depending on the number of hours work, the expected breaktime plus adds any time that they return early to their desk if they do not take their expected break (15/30 minutes)

Give it a go and let me know
Ian.

 

[friarpop]

Thanks, thats what I needed!!! It worked. The only thing now is that instead of saying "day off" like it was in column M, it now says #value. I would assume this is because of z# not pulling anything because there was no data that day. Your help has been worth more than you know.