-
Excel Help
I'm trying to write an excel sheet that when opened, shows how many parts our company should have made at that particular moment (target). We do a 3 shift pattern with 3 sessions each day, 3 lines within our shift all running at different speeds and efficiency. I've got the sheet working as I want it except for one of the shifts, as our night shift starts before midnight and finishes at 7am I cant seem to get the formula working to work out how many minutes have been worked in the first session as this is the session that goes past midnight. I have attached the file so it becomes easier to understand. All my formulas and working out is done on the 'Formulas' tab, the cells coloured green may have to be altered on occasion so if the shift pattern times change, the sheet will automatically adjust to give the correct target. The targets tab is where the info is simplified and shown.
-
Re: Excel Help
Try this @ E24 on the 'formulas' sheet:
=IF(AND($B$20>B24,$B$20<"23:59:59"),((HOUR($B$20-B24))*60)+(MINUTE($B$20-B24)),IF(AND($B$20<B24,$B$20<C24),((HOUR(($B$20+1)-B24))*60)+(MINUTE(($B$20+1)-B24)),D24))
-
Re: Excel Help
Thanks, that seems to work for the first session but now the 2nd and 3rd sessions are wrong, I didnt notice before. Im looking at them now to see if i can apply the same 'formula'
Yep got them all sorted and added a sunday nightshift which had 4 sessions and was a longer shift. Took a while but got there. Cheers again pattikins.
-
Re: Excel Help
OEE oh how much fun I have had with that subject ;-)
Been doing some consultation work for a small company and implanting an OEE dashboard in Excel (as a prototype with the idea of transferring to ASP/SQL) but the company has just been bought out, the larger company is pretty impressed with what they are running so might get quite a bit more work ;-)