The equation you use to calculate the time for each shift doesn't work the way you think it does. I think what you are trying to do is to account for those shifts that span midnight and thus would seemingly give negative times if you subtracted the start from the finish. To this end, you add 24 to the end time and then perform the operation. This is both unnecessary and produces a completely erroneous result.

Excel stores dates/times as floating point numbers with each day equal to one and hours/minutes/seconds as appropriate fractions of one. January 0, 1900 (yep, 1/0/1900) is zero and each day thereafter is one more. Thus when you add 24 to a time, you are not adding 24 hours, you are adding 24 days. This will give you incorrect results any time an employee has an N4 shift.

For the table you have of the shifts with starts and finishes, it's all fine except for the one shift that spans midnight. All times are in h:mm format. But there is hidden meaning to the data: the start time for F1 of 6am is actually 6am on 1/0/1900. That may sound odd, but for the purpose of your calculations, it is sufficient and changing to any other date, while not affecting the result, is unnecessary.

Thus what you need to do is set the time for the end of N4 to be 6:36am on 1/1/1900. You just need to make sure the format is h:mm and the superfluous dates will be invisible and the equations will be seamless. Thus all your equations could be something like:

=IF(AND(G7>0,H7>0),H7-G7,"")

I've just changed it for those F1 - N4 in the attached file.

I did an equation for the first employee. It uses a vlookup for each shift so it's a tad kludgy, but it works as far as I can tell. The hours month to date are just over 100.

=COUNTIF(I9:AQ9,"F1*")*VLOOKUP("F1",Lists!F$7:I$52 ,4,FALSE)+COUNTIF(I9:AQ9,"F2*")*VLOOKUP("F2",Lists !F$7:I$52,4,FALSE)+COUNTIF(I9:AQ9,"S1*")*VLOOKUP(" S1",Lists!F$7:I$52,4,FALSE)+COUNTIF(I9:AQ9,"S2*")* VLOOKUP("S2",Lists!F$7:I$52,4,FALSE)+COUNTIF(I9:AQ 9,"S5*")*VLOOKUP("S5",Lists!F$7:I$52,4,FALSE)+COUN TIF(I9:AQ9,"N4*")*VLOOKUP("N4",Lists!F$7:I$52,4,FA LSE)

Perhaps someone else can figure out a better way. The two factors, hours and count, lend themselves nicely to a simple sumproduct equation which I've done to the right of the above, but it would require a bit of redesign. There certainly are other ways too.

But look this over and let me know if I'm on the right track. If you want we can explore more options.

Attached Files

Bookrstr1.xlsx (37.6 KB, 22 views)