Stop when a value is reached

[tommycasper]

Hi all
Ive done a spreadsheet but part of it just wont work as much as I try.
Range H40:H65 represents Holidays taken. Range I40:I65 totals them up at 8 hours per day, Range J40:J65 is hidden with the formula eg: =H40/3 down the range then Range I40:I65 shows the result.
This works great but my problem is I want the calculation to stop when 21 day are reached even though the holidays exceed 21 days down the range. its also to inclue all the 21 days in the calculation eg: if half way down the range 19 days have been used and the next cell down 5 days is added only 2 of these days is to be calculated as the 21 days threshold has been reached, any holidays after that should show 00:00 down the range.
I hope this makes sense.
Ive attached a sample of what Im stuck with.

[oeldere]

The green cells in #6 in the link below will do that for you.

http://www.excelforum.com/excel-form...o-a-value.html

You should better stayed in the same thread, according to the forumrules.

[oeldere]

And here is also given a solution to your problem.

http://www.excelforum.com/excel-form...o-21-only.html

[tommycasper]

Sorry Oeldere but this problem is giving me a headache and i was trying to find other ideas by explaining it differently, ive put more info on my sample to explain a bit more...

Link: http://we.tl/iuVDSFRwNY

[oeldere]

Please don't use extended sites to post your file.

Not all members want or are allowed to use (download) this files.

You can post it on this forum, as you already did before.

[tommycasper]

Hi Oeldere Yes the solution is there but in numbers format not time format which screws up the rest of my spreadsheet as i use averages in my main spreadsheet which is giving me results of eg: 47 hours and 75 minuets when i need it to show 48:15... If you see the solution and change the cells to a time format it goes crazy so this is why i started a new thread showing my example in a time format rather than a number format.
I hope this makes sense Oeldere, it must be in a time format to work.
Just a question though is it possible to put the ":" in between the time entry automatically on each cell without having to manually enter it????

Chris

[tommycasper]

Sorry didn't realise, I'll attach it...

[oeldere]

See the attached file.


Please post the translate formula in I40 on this forum.

[tommycasper]

Thanks Oeldere sorry for being a pain but the "I" range will have other hours added to it as you will see with the attachment below, which I should have attached originally.
I hope this attachment will give you more insight in what im trying to achive.

Chris

[oeldere]

See the attached file.

See the green cells.

I invoke a column (I)

[tommycasper]

Thanks Oeldere that works perfectly, Sorry for being a pain....

[oeldere]

Thanks for the reply.

Glad I could help.

You can add rep(utation) to the one who helped you, by clicking on the star.

Will you post the englisch formula on the forum, so other members also can see the given solution.