Nested IF statement for time sheet? 1

[sunil5]

Hi all,

Ive just had a go at designing a timesheet on Excel- ive got the basics (total hours, lunch breaks etc). But the company has a few specific requirements...

If an employee does less than 5 hours per any one day then 1 hour for lunch should NOT be deducted else it is (I have a formula for this). But also if they start at 1pm or after, 1 hour for lunch should also NOT be deducted else it is.

Example:

a)

Start time: 13.00 Finish time: 19.00 == 1 hour should NOT be deducted (employee started at 1pm or after)

Formula used:
=IF(B33>0.54166667, "True_Value", "False_Value")

b)

Start time: 9.00 Finish time: 17.00 == 1 hour IS deducted (more than 5 hours per day)

Formula used:

=IF(((C24-B24)*24)>5, ((C24-B24)*24)-1, ((C24-B24)*24))


c)

Start time: 14.00 Finish time 18.00 == 1 hour NOT deducted (employee started after 1pm and does less than 5 hours- this should only deduct 1 hour and not 2 hours even though is meets both criteria.

Formula used:

Mmm here I need to combine the logic of both formulaes but can't seem to get it right for some reason.

I need to combine both this formulaes and have it calculate on a single cell (called "Total hours worked per day")


I hope this makes sense.

Any help on this would be very much appreciated.

Thanks,

Sunil.

 

[SkipVought]

sunil,

You can use the OR Function
[tt]
=IF(OR(B24<13/24,((C24-B24)*24)>5), ((C24-B24)*24)-1, ((C24-B24)*24))
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[sunil5]

Hi SkipVought- thanks for the reply...

The problem with your formula is that say for example an employee works from 9am to 11am- then it is deducting 1 hour- which it shouldn't. Also if they work 1pm to 9pm- it is again deducting 1 hour and it shouldn't.

Maybe i wasn't clear enough in my original post. Bit difficult to describe without seeing the excel sheet i suppose

If an employee works for example, 9am to 11am this should be 2 hours total worked even though it matched the criteria of being before 1pm. The fact is that they have worked less than 5 hours. Conversely if someone worked 2pm to 10pm - a total of 8 hours- this shouldn't deduct 1 hour, because they started at 1pm or after- even though they worked more than 5 hours.

Not sure how to implement this, is an AND statement involved maybe? I think it may be a combination of OR/AND?

Thanks very much for responding.

 

[SkipVought]

Best to make a Truth Table.

List all the criteria in a methodical way.

List the corresponding results for each criteria.

When you have an nice neat table, post back. It should be apparent how all the criteria relate.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[sunil5]

Hi SkipVought,

Sorry was my stupidness- wasn't thinking logically about things at all!- replacing the 'OR' with an 'AND' in your formula did the trick.

I have certainly learned a lesson. Funny thing is I was taught to think like you mentioned (truth tables, etc.) during my education- but I can't believe it almost goes out the window until your reminded again!!

Anyway once again many thanks- keep up the good work.

Oh yeah- a well deserved star. Even though the solution is 'easy' when your showed how- but hey that's always the case!

 

[bluedragon2]

=IF(AND(C24-B24>5/24,B24<13/24),(C24-B24-1/24)*24,(C24-B24)*24)

Will this work for you?

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Davidprince]

Whilst SkipVought's suggestion will work if you look up nested if statements (AND and ORs). It contains several examples which should help solve your problem.