Time

[Peterkroon]

Good morning all
I use a spreadsheet to do my shift updates at work but I am finding the other shifts are entering false hours to make things look good. So what I would like to do is pull the hours from the start time and finish times the issue I am having is some production runs finish a couple of hours into next shift example day shift start 6am finish 2 pm arvo start 2 pm til 10 pm nights 10 pm til 6 am so eg product A starts 8 am and finishes 4 pm I need to show 6 hrs for day shift and 2 hours for arvo shift instead of writing it in manual cause the cases are calculated by the hrs that are run on the shift this will stop people entering false hours to improve their case count. Also when I pull the hours it still has a colon after the number which causes issues in the case calculations. Hoping someone can help

 

[Peterkroon]

thanks for that the formula seems to work but i will need to format it to a number with out a colon so then i can use that number to calculate the planned cases for the hours. i all ready have the cases formula done

 

[Peterkroon]

Just did a test on that formula it seems to work ok but when o change the start time or finish time it has the wrong hours eg if the product only runs on day shift let’s say 6 am till 1 pm it still shows 8 hours and more for night shift

 

[Peterkroon]

Hi all still trying to get this sorted the formula you gave me seems to work ok for day shift and afternoon shifts just not night shift I’ve tried making a few changes to it like getting it to check and see if the shift end time is greater then the actual production time but that only seems to work for day shift

 

[Yongle]

I am unable to download your workbook (using mobile phone) so don't know what your "time" cells contain.
If doing any calculations with time it is usually easier to have BOTH date AND time in the cell formatted to show time allowing Excel to calculate the time interval correctly itself

 

[steve the fish]

Your spreadsheet has a problem if any product runs for more than 24 hours. As yongle says you then will require date. If it never runs for more than 24 hours you could use:

=MIN(D21+(C21>D21),M9+(L9>M9))-MAX(L9,C21)

If it does then you need to use the date too.

 

[Peterkroon]

Thanks mate this works well appreciate your time