total sum of hours gathered per day

[paulalvin]

I cannot figure out what is the problem in my formula. I am only getting correct answer if all the hours is equal to 1.

My formula

=IF(AND(B12<$O$17, C12>=$O$16), $O$21, IF(AND(B12<$O$17, AND(C12>=$O$17, C12<=$O$13)), C12-$O$17, IF(AND(B12<$O$17, AND(C12>$O$13, C12<$O$14)), $O$20, IF(AND(B12<$O$17, C12<= $O$17 ),$O$22, IF(AND(B12<$O$17, AND(C12>=$O$13, C12<=$O$16)), (C12 - $O$17)-$O$19, IF(AND(AND(B12>=$O$17, B12<=$O$13), AND(C12>=$O$17, C12<=$O$13)), C12-B12, IF(AND(AND(B12>=$O$17, B12<=$O$13),AND(C12>$O$13, C12<$O$18)), $O$13-B12, IF(AND(AND(B12>=$O$17, B12<=$O$13),AND(C12>=$O$18, C12<=$O$16)), (C12-B12)-$O$19, IF(AND(AND(B12>=$O$17, B12<=$O$13), C12>$O$16), ($O$16-B12)-$O$19, IF(AND(AND(B12>$O$13, B12<$O$18), AND(C12>=$O$18, C12<=$O$16)), C12-$O$18, IF(AND(AND(B12>$O$13, B12<$O$18), AND(C12>$O$13, C12<$O$18)), $O$22, IF(AND(AND(B12>=$O$18, B12<=$O$16), AND(C12>=$O$18, C12<=$O$16)), C12-B12, IF(AND(AND(B12>$O$13, B12<$O$14), C12>$O$16), $O$20, $O$16-B12)))))))))))))

[TMS]

No idea and not much to work on.

I'd start by removing the superfluous ANDs ... you don't need AND(AND(... unless some of the ANDs should be ORs.

Then use Evaluate Formula to see what's going on.

If you want further help, I suggest you post a sample workbook indicating what you expect to see based on the input to the formula.

Regards, TMS

[paulalvin]

how can I attach my document here for you to check?

[TMS]

Go Advanced; Manage Attachments or click on paperclip icon.

[paulalvin]

Ok here is it

[paulalvin]

try having a time in as 8:00 and time out as 17:00 then the total will be 15. If you change the time in and time out the total hours are definitely wrong

[TMS]

try having a time in as 8:00 and time out as 17:00

Why? That looks like an 8 hour working day with 1 hour for lunch. And your formula, surprisingly, returns 8 hours Regular Time. Where do you get 15?

If I'm honest, I think this is way over-engineered. I'd start with the basics ... finish time - start time ... and then build up the factors individually. At least then you'll see where it starts to go wrong. I usually start with simple formulae for each element then combine them into a complex formula once I know each bit works.

But start by removing the redundant ANDs; that will reduce the size of the formula and make it more readable.

Sorry, as it stands, I'm out.

Regards, TMS

---------- Post added at 11:03 AM ---------- Previous post was at 10:59 AM ----------

Oh, and you need to format Total Hours in D27 as [h].mm ... well, in fact, any hours that will exceed 24 need to be formatted that way.

Regards, TMS

[paulalvin]

Thanks TMShucks It solved my problem
the only solution is provide [h] in h.mm thanks a lot

[Cutter]

@ paulalvin

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.