Thread: Calculating basic hours worked, between a time range, problems

I am trying to set up a time sheet to work out site basic hours, these are between 07.00 and 18.00, the problem I have is with the summing up, basically my problem is indicated below:-

If I work 06:00 to 17:00 this should be 10 hours site basic and 1 hour premium hours, but the below sum calculates the site basic hours to 11
Or if I work 09:00 to 20:00 this should be 9 hours site basic and 2 hour premium hours, but the below sum calculates the site basic hours to 11

=IF(AND(J36="",M36=""),"",(IF(AND(Table_Data!J1),"",IF(AND(J36<=7,M36>18),(18-7),M36-J36))))

Site basic hours 07.00-18.00 Monday to Saturday. Premium rates 18.00-07.00 Monday to Saturday

1) Go into TOOLS > OPTIONS and find the [x] Zero Values settings, uncheck that. This applies to the active sheet only, it's not a global setting.

2) Now use these formulas:

W36: =MAX(0,SUM(T36-G36))
Z36: =MAX(SUM(J36-G36)+(T36-M36),0)
AC36: =MAX((M36-J36)-MAX("7:00"-J36,0)-MAX(M36-"18:00", 0), 0)
AF36: =IF(W36=0, 0, MAX("7:00"-J36,0)+MAX(M36-"18:00", 0))

Copy those down.

AI42: =IF(W42=0, 0, SUM(T42-G42))

Times going past midnight actually is a significant piece of added info.

For holidays, on your Table_Data sheet add the list of dates that are your holidays into column K, just list them downwards, any dates you want.

Then use these sets of formulas:

W36: =MAX(0,SUM(T36-G36)+(G36>T36))
Z36: =MAX(SUM(J36-G36)+(T36-M36)+(M36>T36),0)
AC36: =IF(ISNUMBER(MATCH(A36, Table_Data!$K:$K, 0)), 0, MAX((M36-J36)-MAX("7:00"-J36,0)-MAX(M36-"18:00", 0), 0))
AF36: =W36-Z36-AC36

Copy those formulas down.

AI42: =IF(W42=0, 0, SUM(T42-G42))