Total hours worked each day

Hi,

I need a formula to show total hours worked each day. The formula I currently use counts the number of cells populated but since changing start and finish times to include quarter to and quarter past the hour I am now getting an inaccurate calculation.

I have attached a sample workbook, Sheet 1 column Y is where the formula is needed. As you can see Y5,Y6,Y13 and Y14 are displaying 8.5 hours when they should be 7.5. I can't pull this data from columns V and W as when cells are populated OUT or H these times are not displayed.

All other calculations in column Y are correct and for example, where F3:M3 shows 8 hours worked this includes a half hour unpaid break J3 so total paid hours in column Y is 7.5

D5:L5 is 9 populated cells including I5 the half hour unpaid break, but as can be seen in V5:W5 there is only 8 hours at work and taking off I5 leaves 7.5 total hours paid.

There is a legend on Sheet 3 to show what all the colors represent.

Thanks for the reply, perhaps I didn't explain correctly as the formula I have is bringing back incorrect results for what I need.

As you say, what my current formula does is correct but in relation to D5:L5, there is still only a work time of 7.5 hours not 8.5 as the start finish times dictate in V5:W5

Tez

That would still show result of 8 hours paid for C5:T5 but should be 7.5 as worked times are 6.45-14.45 then taking off half hour unpaid (LL)

If you recorded the hours that are worked in each hour it would work. The way you are currently recording means 0.75 can take different values depending where it appears

I interpreted d5 to mean 0.75 hours were worked, not the start time was 6:45

I think I just fixed it but thanks any way guys.

I added "0.75","0.25" to my original formula

=IF(A5="","",COUNTA(C5:T5)-(SUM(COUNTIF(C5:T5,{"LL","LE","H.5","OUT.5","0.75","0.25","0.5"})/2)))

Probably not the best way but gets the result I'm after, really don't know why it took me so long to think of it lol

be careful what you wish for, as the above approach would be more simplistic, now you have to evaluate the first and last value in the row and adjust accordingly if it is <1

=IF(A3="","",COUNTA(C3:T3)-(SUM(COUNTIF(C3:T3,{"LL","LE","H.5","OUT.5","0.5"})/2)))+ IF(INDEX(C3:T3,MATCH(TRUE,INDEX((C3:T3<>0),0),0))<1,-INDEX(C3:T3,MATCH(TRUE,INDEX((C3:T3<>0),0),0)),0)-IF(LOOKUP(2,1/(C3:T3<>""),C3:T3)<1,1-LOOKUP(2,1/(C3:T3<>""),C3:T3),0)

your formula only works as the start and the end add up to one full hour what if l5 was 0.25?

I see I was a little to quick to think I fixed my problem

I think the formula in Y needs discarding and starting from scratch

And how would I do that, after all I did come here for help!

The first question is what is wrong with the answers already provided


if you change how the hours are recorded post 6 works, if you insist on recording as you are post 10 works

If they don't tell us where they don't.

If they do then mark the thread as solved

My apologies, took me a while to understand what you were meaning by recording the time differently.

D5 being 15 minutes worked = 0.25
L5 45 minutes worked = 0.75

I was calculating as time so 6.45 would be 0.75 past the hour, thats what confused me.

Post #6 so far so good, thanks for your help and patients