It's about TIME

[maeyks]

Dear All,

I have the following time related question:

1) If I will identify 10:00p.m. <gs id="afbcd3c5-b60c-4b00-97bc-8f8af87e93ba" ginger_software_uiphraseguid="6d7f5f65-5bff-463c-af0f-9f8b6ae9c6cf" class="GINGER_SOFTWARE_mark">as</gs> start of my night differential rate, with a minimum of 1 hour, therefore 11:00pm as its start, and increments every 30 minutes? For example, 10:00pm-11:00pm it means 1 hour night differential, and on 11:30, 12:00, 12:30, 1:00 so on and so forth

2) I am having difficulty in getting the total number of hours because it recognizes the AM and <gs id="5bf475c8-695f-454a-be82-fbf93f4a0774" ginger_software_uiphraseguid="fc7c31cb-802b-410a-8200-512ad1f76a9a" class="GINGER_SOFTWARE_mark">PM time</gs> for those which will pass the 24 hour mark; for example my work schedule is 8:00am to 5:00pm (8 hours); my actual work <gs id="e828ed1e-71b9-4b06-9090-8680db9d7073" ginger_software_uiphraseguid="fc7c31cb-802b-410a-8200-512ad1f76a9a" class="GINGER_SOFTWARE_mark">renderred</gs> is 7:56 am to 1:00 am the next day. I need to:
2.a) <gs id="a894c24f-b54c-4ddc-8506-8d31dbd4dabc" ginger_software_uiphraseguid="573d8d27-7989-4c16-87d0-762b7d7fed28" class="GINGER_SOFTWARE_mark">compute</gs> the regular work hours (7:56 am to 5:00 pm as 8 hours)
2.b) <gs id="a10b8de7-641f-4a76-9820-99ef9e15f7b5" ginger_software_uiphraseguid="0bebb344-9a26-4afa-984a-6a2877e048d2" class="GINGER_SOFTWARE_mark">compute</gs> the regular over time (from 5:00 pm to 1:00 am the next day as 8 hours)
2.c) <gs id="a3da8838-9864-4056-b4b9-56b34f784acf" ginger_software_uiphraseguid="e7f378b3-d1a8-4447-9222-72765b51e2e0" class="GINGER_SOFTWARE_mark">compute</gs> the night differential (from 10:00 pm to 1:00 am the next day as 3 hours)
2.d) <gs id="c5a3e031-a3fa-40ef-894f-50df8fcc0083" ginger_software_uiphraseguid="d0fc2cc6-8a1c-4402-9cef-340cc1ed9fec" class="GINGER_SOFTWARE_mark">compute</gs> the TOTAL work hours (from 7:56 am to 1:00 am the next day which is 16 hours)

Thank you!!!

 

[admiral100]

Hi ,

Where is the question mark

Let say A1 = 7:56 am and B1 = 1:00 am

=INT(((A1-B1)*1440)/24)

 

[Andrew Poulsom]

Does this work for you:

=MOD(B1-MAX(A1,"08:00"),1)

with start time in A1 and end time in B1? Surely 8AM to 5PM is 9 hours not 8.

 

[maeyks]

Does this work for you:

=MOD(B1-MAX(A1,"08:00"),1)

with start time in A1 and end time in B1? Surely 8AM to 5PM is 9 hours not 8.

Sorry, I forgot to mention, the 1 hour is for their break time. Thanks!