Accumulated Time Formula

[pippip]

Hi, any help would be much appreciated. I've looked for a solution but can't find something that meets the specific needs. I need a formula that counts up the accumulated hours per day in a time sheet, then calculates the total lunch time taken during a week and subtracts it to give me the net total weekly working hours.

The layout is:

Col A: name
Cols B - K: start & end times each day, e.g. 09:00, 17:00, 08:45, 15:30, etc
Col L: time allowed for lunch each day e.g. 30 minutes (it's the same amount each day regardless of start and end times.)
Col M: Net total weekly working hours after subtracting total lunch times

Seems to me there's two issues: 1, how to express the lunch minutes (can't put 00:30 because that would just mean half past midnight wouldn't it?); and 2, the formula for column M.

Thanks in advance for you help.

Craig

 

[Mike Blackman]

Hi,

Dpending on how the lunch minutes are entered...

In 00:30 format:

=(ROUNDDOWN(COUNTA(B1:K1)/2,0)*(L1*1440))

In whole minutes - 30:

=(ROUNDDOWN(COUNTA(B1:K1)/2,0)*L1)

 

[pippip]

Thanks for that Mike, but when I enter the rounddown formula I get a result of 00:00. All the cells are custom formatted hh:mm.

 

[Mike Blackman]

The roundown only effects the COUNTA not the times...

Did you try both formula? The first:

=(ROUNDDOWN(COUNTA(B1:K1)/2,0)*(L1*1440))

Multiplies the times into whole minutes so you see a value of 1 for every minute.

The second:

=(ROUNDDOWN(COUNTA(B1:K1)/2,0)*L1)

Will show it as a time format as its not multiplied up from its decimal value.

 

[pippip]

(Been away for a while hence the delay in responding.)

Thanks again Mike, but I still can't get it to work. At a simple level I've got some people who work 9am to 5pm, five days a week and take 30 minutes break each day. That comes to a total of 37.5 hours. Using the first formula the nearest I got was 36 (got this by adding /60 at the end of the formula as you said it returned the total in minutes). Using the second formula I get a result of 0. By the way the result doesn't seem to change no matter how many hours a day a person works.

Is there a way I can post a screenshot or even a draft worksheet?