Nesting 2 IF Statements - Different Clocking in Data

[esgca2010]

Hello,

I have been trying to combine 2 IF statements for 2 hours and have tried multiple variations and researched the board for answers but still can't get it to work.

Nested IF statement in cell F6. Times are in range D3:D6. If the times are not filled in then leave blank.

Scenario: Employees Time in and Timeout multiple times as the example below:

1st Example: Clocking Record
Time In	8:00 AM
Time Out	10:00 AM
Time In	2:00 PM
Time Out	6:00 PM

<colgroup><col><col></colgroup><tbody>
</tbody>

Total Hrs Worked

6

<tbody>
</tbody>

2nd Example: Employees Time in and Timeout once as the example below:

Clocking Record
Time In	8:00 AM
Time Out
Time In
Time Out	6:00 PM

<colgroup><col><col></colgroup><tbody>
</tbody>

Total Hrs Worked

10

<tbody>
</tbody>

Start Time – End Time – Start Time – End Time
=IF(AND(COUNT(D3:D6)=4),MROUND((D4-D3+(D4<=D3))*24,0.25)+MROUND((D6-D5+(D6<=D5))*24,0.25),"")

Start Time – End Time
=IF(MROUND((D6-D3+(D6<=D3))*24,0.25),MROUND((D6-D3+(D6<=D3))*24,0.25),"")

Thank you so much for your support.

Elaine

 

[admiral100]

Hi,

Try this

=IF(COUNT(D3:D6)=4,MROUND((D4-D3+(D4<=D3))*24,0.25)+MROUND((D6-D5+(D6<=D5))*24,0.25),MROUND((D6-D3+(D6<=D3))*24,0.25))

 

[etaf]

is that the only combination
so if its NOT a count of 4 - then its FALSE and must be a count of 2

=IF(AND(COUNT(D3:D6)=4),MROUND((D4-D3+(D4<=D3))*24,0.25)+MROUND((D6-D5+(D6<=D5))*24,0.25),MROUND((D6-D3+(D6<=D3))*24,0.25)))

dont need the AND ()

Or if it could have NO entries

=IF(COUNT(D3:D6)=0,"",IF(COUNT(D3:D6)=4,MROUND((D4-D3+(D4<=D3))*24,0.25)+MROUND((D6-D5+(D6<=D5))*24,0.25),MROUND((D6-D3+(D6<=D3))*24,0.25)))

 

[esgca2010]

Hello admiral100,

You are awesome. It works but if the start and end times are blank it shows 24. How do I revise your formula to show the cell blank if there is no data?

Once again, thank you so much! I truly appreciate your time and effort to help me.

Respectfully,

Elaine

 

[esgca2010]

ADVERTISEMENT

Hello etaf,

I the statement works but when I enter the start time with NO end time it shows 16. The correct answer appears when both entries are made. Is there a way for it to be blank until there is a start time AND end time?

First Example:

Time In	8:00 AM
Time Out	12:00 PM

<colgroup><col><col></colgroup><tbody>
</tbody>

Shows result as 16

2nd example:

Time In	8:00 AM
Time Out
Time In
Time Out

<colgroup><col><col></colgroup><tbody>
</tbody>

Shows result as 16

3rd Example:

Time In	8:00 AM
Time Out	11:00 AM
Time In	12:00 PM

<colgroup><col><col></colgroup><tbody>
</tbody>

Shows result as 16

------------------------------------------------------------------
When filled correctly:

Time In	8:00 AM
Time Out	11:00 AM
Time In	12:00 PM
Time Out	4:00 PM

<colgroup><col><col></colgroup><tbody>
</tbody>

The result is total hours worked 7
------------------------------------------------------
The second was filled in correctly

Time In	8:00 AM
Time Out
Time In
Time Out	4:00 PM

<colgroup><col><col></colgroup><tbody>
</tbody>

Total Hours Worked: 8

When the time is incomplete instead of showing 16 we would like it to be blank.

Thank you so much for your support.

Respectfully,

Elaine

 

[etaf]

replace
COUNT(D3:D6)=0
with
=OR(COUNT(D3:D6)=0,COUNT(D3:D6)=1,COUNT(D3:D6)=3)

=IF(OR(COUNT(D3:D6)=0,COUNT(D3:D6)=1,COUNT(D3:D6)=3),"",IF(COUNT(D3:D6)=4,MROUND((D4-D3+(D4<=D3))*24,0.25)+MROUND((D6-D5+(D6<=D5))*24,0.25),MROUND((D6-D3+(D6<=D3))*24,0.25)))

 

[esgca2010]

Thank you. I can't begin to tell you how much I appreciate your expertise.

Thank you!!!

Elaine

 

[etaf]

you are very welcome