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:
[TABLE="width: 220"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]
1st Example: Clocking Record[/TD]
[/TR]
[TR]
[TD]Time In[/TD]
[TD="align: right"]8:00 AM[/TD]
[/TR]
[TR]
[TD]Time Out[/TD]
[TD="align: right"]10:00 AM[/TD]
[/TR]
[TR]
[TD]Time In[/TD]
[TD="align: right"]2:00 PM[/TD]
[/TR]
[TR]
[TD]Time Out[/TD]
[TD="align: right"]6:00 PM[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 391"]
<tbody>[TR]
[TD]Total Hrs Worked[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 220"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]Clocking Record[/TD]
[/TR]
[TR]
[TD]Time In[/TD]
[TD="align: right"]8:00 AM[/TD]
[/TR]
[TR]
[TD]Time Out[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Time In[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Time Out[/TD]
[TD="align: right"]6:00 PM[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 391"]
<tbody>[TR]
[TD]Total Hrs Worked[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

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]

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:
[TABLE="width: 220"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Time In[/TD]
[TD="align: right"]8:00 AM[/TD]
[/TR]
[TR]
[TD]Time Out[/TD]
[TD="align: right"]12:00 PM

[/TD]
[/TR]
</tbody>[/TABLE]
Shows result as 16

2nd example:
[TABLE="width: 220"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Time In[/TD]
[TD="align: right"]8:00 AM[/TD]
[/TR]
[TR]
[TD]Time Out[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Time In[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Time Out[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]
Shows result as 16

3rd Example:
[TABLE="width: 220"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Time In[/TD]
[TD="align: right"]8:00 AM[/TD]
[/TR]
[TR]
[TD]Time Out[/TD]
[TD="align: right"]11:00 AM[/TD]
[/TR]
[TR]
[TD]Time In[/TD]
[TD="align: right"]12:00 PM
[/TD]
[/TR]
</tbody>[/TABLE]
Shows result as 16

------------------------------------------------------------------
When filled correctly:
[TABLE="width: 220"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Time In[/TD]
[TD="align: right"]8:00 AM[/TD]
[/TR]
[TR]
[TD]Time Out[/TD]
[TD="align: right"]11:00 AM[/TD]
[/TR]
[TR]
[TD]Time In[/TD]
[TD="align: right"]12:00 PM[/TD]
[/TR]
[TR]
[TD]Time Out[/TD]
[TD="align: right"]4:00 PM[/TD]
[/TR]
</tbody>[/TABLE]
The result is total hours worked 7
------------------------------------------------------
The second was filled in correctly
[TABLE="width: 220"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Time In[/TD]
[TD="align: right"]8:00 AM[/TD]
[/TR]
[TR]
[TD]Time Out[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Time In[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Time Out[/TD]
[TD="align: right"]4:00 PM[/TD]
[/TR]
</tbody>[/TABLE]
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