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]

I looked into this further and the formula isnt up to par yet, almost. I noticed I have a rep who worked 8 hours and took a 45 minute break instead of 30. Instead of their expected time as 7:30, it showed up as 7:15? It works to add break underages back in but any overages shouldnt be taken out. Is it possible to have that work with the current formula?

 

[crook_101]

Morning,

Too tired last night!! Slight ammendment to the formula in "Z, at the start of the formula, add in a zero as below in red:

Before: =IF(B3="","",etc

To: =IF(B3="","0",etc etc etc

This will cure the day off thing

Cheers,
Ian,

 

[crook_101]

Will take a look at this later today - proper work to do now

 

[crook_101]

Friarpop - that was a bit of a challenge The below will work as you requested if you just change the formula in M3 & Z3 and drag down....I am sure that there is a better way of doing this but it has been a bit of a week

If you need anything further, please post!!

Ian.

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	04:00:00			00:10:00								03:50:00	00:05:00
4	08:00:00			00:16:00								07:44:00	00:14:00
5	08:00:00			00:45:00								07:30:00	0
Sheet1

 

[friarpop]

Thanks again crook! Its working great.
One last thing that I noticed that I really need fixed.

Example:
Say that someone works but is late and has 9:55:00 staffed time, it should be 10:00:00 but they were 5 minutes late. say also they didnt take any break so they are expected 10:00:00 but with the current formula it shows at 9:55:00. I would like it to say 10:00:00 expect. It works with overages, for instance if they work 10:05:00 and were five minutes early they would be expect at 10:05:00 for their shift if they didnt take a break but the underages when they are late comes out of the expected time... anyway to fix this last thing? Sorry if that is confusing. I sent you an email a few days ago but dont think I got a response unless its in my spam. I'm assuming I will need a reference cell for each day with the time that they are expected to work for this to draw off of? Currently we manually type in the expected time in Column M but I replaced it with your auto calculating formula. I can always move your formula calculated column over one more spot and have it reference what I write in to determine if its over or under?

 

[crook_101]

Hi mate,

Had a look at this and I think, without reverting to VBA code, the only thing you could do is to enter it as below, this will round the expected time to the nearest half hour if a "Y" (not case sensitive) is entered into column O.

Emp is 10 minutes late on a 10 hour shift, no breaks so the expected would show as 00:09:50, once you enter the "Y", this will round it to 00:10:00 in column N. THe rounding will take it to the closest half hour - 07:46 = 08:00, 07:44 = 07:30 - this can be changed if required to always round up......you may wish to conditional format this and this will highlight any employees with persistant lateness "issues"

NB - All of the other stuff stays in place in "Z" and the M40,41 things etc, the only changes in this sheet are in the formulea & headers in columns O & N.

Take a look, have a play and let me know - I realise that this thread keeps getting buried so just drop me a pm and I will take another look if there's anything else.
Have a great weekend,
Ian.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Sky Text,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 125px"><COL style="WIDTH: 85px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>M</TD><TD>N</TD><TD>O</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">expected</TD><TD>Rounded Time</TD><TD>Emp Late</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">03:50:00</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">04:00:00</TD><TD style="TEXT-ALIGN: center">y</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">07:44:00</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">07:30:00</TD><TD style="TEXT-ALIGN: center">y</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">09:55:00</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">10:00:00</TD><TD style="TEXT-ALIGN: center">y</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>M3</TD><TD>=IF(ISERROR(IF(B3<$L$40,B3,IF(B3<$L$41,(B3-$M$40),(B3-$M$41))+Z3)),"Day Off",IF(B3="","Day Off",IF(B3<$L$40,B3,IF(B3<$L$41,(B3-$M$40),(B3-$M$41)))+Z3))</TD></TR><TR><TD>N3</TD><TD>=IF(O3="Y",MROUND(M3,1/48),"")</TD></TR><TR><TD>M4</TD><TD>=IF(ISERROR(IF(B4<$L$40,B4,IF(B4<$L$41,(B4-$M$40),(B4-$M$41))+Z4)),"Day Off",IF(B4="","Day Off",IF(B4<$L$40,B4,IF(B4<$L$41,(B4-$M$40),(B4-$M$41)))+Z4))</TD></TR><TR><TD>N4</TD><TD>=IF(O4="Y",MROUND(M4,1/48),"")</TD></TR><TR><TD>M5</TD><TD>=IF(ISERROR(IF(B5<$L$40,B5,IF(B5<$L$41,(B5-$M$40),(B5-$M$41))+Z5)),"Day Off",IF(B5="","Day Off",IF(B5<$L$40,B5,IF(B5<$L$41,(B5-$M$40),(B5-$M$41)))+Z5))</TD></TR><TR><TD>N5</TD><TD>=IF(O5="Y",MROUND(M5,1/48),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[friarpop]

Hi Ian, hope you are starting to feel better. I gave it a shot and it give me an error #Name? ... looking at the formula for N, what does the 1/48 reference just out of curiosity? I tried to format the column for time and it still didnt work. I also tried using a Capital Y & lower case y in the O column. Couldnt figure it out? Anyway I hope you have a great weekend also. I will be around for another hour or so. If not I will catch up with you next week.

 

[crook_101]

Hi,

Feeling a bit better thanks

Try using the format painter, click on M3, fomat painter then M4, see if that works.....you may need the analysis tool pack add in for this, depending what version of excel you are running - Tools>>Add-ins>>Analysis ToolPak

The 1/48 references rounding to the nearest half hour (48 half hours in 24 hours) - it amazed me when I learnt that too!!

If that fails, let me know - I need some sleep now but will catch up next week.

Cheers,
Ian.

 

[friarpop]

Thanks Ian, I do have that plugin. I had to download it from my companys files. Nice to know that they have some plugins available. I was able to use the painter and it worked. I will do some audit work to see how things look and let you know of any problems. I will talk to you later, have a great weekend. Thanks again pal.