This formula =IF(A1=0/24,IF(B1<=4/24,B1-A1,4/24-A1),IF(B1<=4/24,B1-0/24,4/24-0/24))
works GREAT except when the start time is before midnight. If cell A1 (start time) shows 23:00 and B1 (end time) 4:00 the end result is -0.79.
Any ideas how I can get this to work when crossing midnight?
Quote:
Originally Posted by Shane Devenshire[_2_]
Hi,
Suppose the Start Time is in A1 and the End Time in B1 then
=IF(A1=15/24,IF(B1<=23/24,B1-A1,23/24-A1),IF(B1<=23/24,B1-15/24,23/24-15/24))
for times betwen 3 - 11 PM
=IF(A1B1,IF(B1<=7/24,B1,7),IF(B1<=7/24,B1-A1,7/24-A1))
--
If this helps, please click the Yes button
Cheers,
Shane Devenshire
"Lisa Kerr" wrote:
Need to get total of hours within start and end times on a timesheet that qualify for shift differential.
I modified C2 to find the total number of hours worked in a shift after 11 PM & it worked!
But...I will also need to find the total number of hours worked between 3 PM and 11 PM on given days.
And the total number of hours worked between midnight and 7 AM...so the inverse of C2 where I needed to find total hours worked after 23:00, I will need the total number of hours worked prior to 7 AM.
Thank you in advance for any feedback! I have been searching for a formula such as C2 for hours.
|