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

 

[The_Macrotect]

Hi Peterkroon,

I'm having trouble picturing it. Can you post an example spreadsheet?


-Matt

 

[Peterkroon]

Hi Peterkroon,

I'm having trouble picturing it. Can you post an example spreadsheet?


-Matt

<tbody> </tbody>

can you please tell me how to attach a file in this forrum

<tbody> </tbody>

<colgroup><col><col><col><col><col span="7"><col><col><col span="10"></colgroup><tbody>
</tbody>

 

[Peterkroon]

no having any luck trying to post a sample mate even as a picture

 

[Michael M]

Have a look at the HTML Maker in my tag OR simply upload the workbook to DropBox and post a link back here !!

 

[Peterkroon]

https://www.dropbox.com/s/qcuml449j4c8bvd/sample to shift update.xlsx?dl=0

see if this works this is just a sample

 

[Michael M]

How can the result be 6 hours

22:00-14:00......8 hours
OR
8am-1am.....17 hours

neither will be 6 hrs

 

[Peterkroon]

in the sample i uploaded where it says afternoon shift it is a drop down box if you select day shift it will change the times to 6 am to 2 pm EG product A is meant to start at 8 am so this will give you 6 hours of production time 8 am to 2 pm is 6 hours if you change the drop down to afternoon shift this will give you a time of 2 pm to 10 pm so thats a production run time of 8 hour a full shift for afternoon since the product has to run from 8 am to 1 am so that will make night shifts production time of 3 hours EG 10 pm to 1 am instead of it running full shift 10 pm to 6 am
i hope this makes sense

 

[The_Macrotect]

If I understand correctly, this formula in cell F21 in your sample should do the trick:

=IF(J9="Day Shift",M9-C21,IF(J9="Afternoon Shift",M9-L9,IF(J9="Night Shift",24-(L9-D21))))

 

[Peterkroon]

Ok mate I’ll give it a try and let you know thanks for your help