Nesting 2 IF Statements - Different Clocking in Data

esgca2010

New Member
Joined
Jul 29, 2010
Messages
29
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 In8:00 AM
Time Out10:00 AM
Time In2:00 PM
Time Out6:00 PM

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

Total Hrs Worked6

<tbody>
</tbody>

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

Clocking Record
Time In8:00 AM
Time Out
Time In
Time Out6:00 PM

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

Total Hrs Worked10

<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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

admiral100

Well-known Member
Joined
Jan 17, 2015
Messages
873
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,426
Office Version
  1. 365
Platform
  1. MacOS
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

New Member
Joined
Jul 29, 2010
Messages
29
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

New Member
Joined
Jul 29, 2010
Messages
29

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 In8:00 AM
Time Out12:00 PM


<colgroup><col><col></colgroup><tbody>
</tbody>
Shows result as 16

2nd example:
Time In8:00 AM
Time Out
Time In
Time Out


<colgroup><col><col></colgroup><tbody>
</tbody>
Shows result as 16

3rd Example:
Time In8:00 AM
Time Out11:00 AM
Time In12:00 PM

<colgroup><col><col></colgroup><tbody>
</tbody>
Shows result as 16

------------------------------------------------------------------
When filled correctly:
Time In8:00 AM
Time Out11:00 AM
Time In12:00 PM
Time Out4:00 PM

<colgroup><col><col></colgroup><tbody>
</tbody>
The result is total hours worked 7
------------------------------------------------------
The second was filled in correctly
Time In8:00 AM
Time Out
Time In
Time Out4: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

Well-known Member
Joined
Oct 24, 2012
Messages
4,426
Office Version
  1. 365
Platform
  1. MacOS
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

New Member
Joined
Jul 29, 2010
Messages
29
Thank you. I can't begin to tell you how much I appreciate your expertise.

Thank you!!!

Elaine
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,426
Office Version
  1. 365
Platform
  1. MacOS
you are very welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,123,381
Messages
5,601,302
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top