Count hours for given period

[weekendwarrior]

Hi,

Wondering if someone might come to the rescue with this.:-)

For any 10 day period an employee may accumulate 15 hours of labour i.e. 1st march might be 5 hours, 3rd March might be 5 hours and 5th March 5 hours.

As soon as an employee reaches 15 hours of labour for any 10 day period the words 'Anchor Date' is to be placed in a cell in another column.

As you can see by this the 10 day period can shift throughout the month.

Any guidance here much appreciated.

Regards,
John

[teylyn]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[weekendwarrior]

Okay thanks.

Just dates in column A and hours in Column B.

[teylyn]

did you mean to attach a file?

[weekendwarrior]

Yep, here it is.

For the period shown 18/3 to 27/3 there has been only 10 hours accrued.

However for the period 23/3 to 31/3 there has been 15 hours accrued and is within a 10 day period so the rquired entry 'Anchor Date' is to be inserted at the 31/3 date for column B.

regards,
John

[weekendwarrior]

Is there a conditional formula for the COUNTIF function that might scan for 10 day periods here looking for 15 hour increments?

[teylyn]

So you've marked the Anchor date for the ten-day period. What now? The period from 28/3 to 3/4 also has 15 hours. So you want Anchor date to appear on row 17 as well? And on row 18? Because the period of ten days to 4/4 also totals to 15 hours.

See, I still don't quite understand what you want to achieve.

[weekendwarrior]

TBH I think I am being VERY clear, I want the word ANCHOR Date inserted for ANY 10 day period where 15 hours has been accrued, geez!!

[teylyn]

Are you getting lippy? I don't think you are being very clear at all. It took you three posts to manage to attach your file and your mocked up data only shows one "anchor date", so I came back for clarification. You'd better reign in your attitude. We are all volunteers here. I am under no obligation to help you. It is my kind and forgiving nature that provides you with a solution.

in C10 and copied down

=IF(SUM(B1:B10)>=15,"anchor date","")

[weekendwarrior]

Your the one with the attitude mate and cannot understand a clear description.

You come across as being a smart ****, how about YOU check your attitude.

I don't need your help, forget it. I will not be back here.

[weekendwarrior]

....and BTW that solution is no where near correct, it is not a simple sum for a range. You got no idea!!!

[teylyn]

See, you still have not managed to describe your requirements. My formula does exactly what you describe above, i.e. it provides

the word ANCHOR Date inserted for ANY 10 day period where 15 hours has been accrued, geez!!

But seeing that you are not open to a constructive discussion, I believe this place is not for you.

I don't think you will ever post here again. Verbally abusing moderators and people who are trying to provide you witha a solution to your problem, free of charge, and in their own time, is not something we support here. Good bye.