CODEWITHSUNDEEP
excelexcel-formula
Soumya
Soumya

Reputation: 67

MS Excel: Formula is not working Properly

I am newbie to Excel. I am trying to make Attendance Reports.Now, Reports will be calculated as per conditions which are as follows:

Min Range as 'C': 09:30
Max Range as 'D': 19:30

Let us consider, A as In and B as Out

a)If A>C and B<D, then B-A  For Eg: 10:00>09:30  and 19:00<19:30 then O/P is 09:00 
b)If A<C and B<D ,then B-C  For Eg: 09:00<09:30  and 19:00<19:30 then O/P is 09:30
c)If A>C and B>D, then D-A  For Eg: 10:30>09:30  and 20:00>19:30 then O/P is 08:00
d)If A<C and B>D, then D-C  For Eg: 09:00<09:30  and 20:00>19:30 then O/P is 10:00

Excel Formula:

  =IF(AND(B>=C,A<=D),B-A,
    IF(AND(A>=C,B>=D),H55-A,
    IF(AND(A<=C,B<=D),B-H54,
    IF(AND(A<=C,B>=D),"10:00","Wrong"))))

Now,Excel formula is working for some conditions, but for some conditions is not working. Please do suggest me.Thanks in advance.

Upvotes: 1

Views: 62

Answers (2)

EEM
EEM

Reputation: 6659

The criteria described could be summarized as follows:

In Time cannot be earlier than 09:30 and Out Time cannot be later than 19:30.

Therefore In Time = IF(A<=C,C,A) and Out Time =IF(B>=D,D,B)

The final formula should be O/P = Out Time - In Time then try this formula:

=SUM(IF(B>=D,D,B),-IF(A<=C,C,A))

Upvotes: 0

Aditya Pansare
Aditya Pansare

Reputation: 1132

Check this out.

=IF(AND(A>=C,B<=D),B-A,IF(AND(A<=C,B<=D),B-C,IF(AND(A>=C,B>=D),D-A,IF(AND(A<=C,B>=D),D-C,"Wrong"))))

Upvotes: 1

Related Questions