Formula to ignore empty cells when calculating Time

S

stacyl14

New Member
Joined
Aug 17, 2013
Messages
6
First off, I am new to this forum and I just want to thank every one in advance for any and all assistance. I have been searching the web for a week and have not even come close to figuring this out. Here is my problem: We have created a excel (2010) workbook to track workers who are late (see below) to determine lost production. I have managed to get it to calculate the time but only if I fill every cell with either the time the worker arrived or the scheduled work time. This throws off my calculation in column "Q" which I have set to calculate the number of times the worker has been late during the pay period (counts cells with data in them). What I need is a formula which will calculate the time difference (between arrival time and actual start time) in only the cells that have a time entered and ignoring the cells with no time entered, then total them in Column "R" (formatted [h]:mm) . Monday through Friday (columns: E,F,G,H,I,K,L,M,N,O) the start time is 07:30 (located in cell: W2), on Saturdays (columns: J,P) the start time is 06:30 (located in cell: X2).

I hope someone here can understand what I am trying to say.

Excel 2010
EFGHIJKLMNOPQRSTUVWX
12-Sep3-Sep4-Sep5-Sep6-Sep6-Sep9-Sep10-Sep11-Sep12-Sep13-Sep14-SepTOTALMinutes LateLost Production RateLost Production ValueTotal Lost ProductionMon-Fri/ StartSat/ Start
27:356:508:053$82.80007:306:30
300
400

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
Q2=COUNTA(E2:F2:G2:H2:I2:J2:K2:L2:M2:N2:O2:P2)
Q3=COUNTA(E3:F3:G3:H3:I3:J3:K3:L3:M3:N3:O3:P3)
Q4=COUNTA(E4:F4:G4:H4:I4:J4:K4:L4:M4:N4:O4:P4)
T2=R2*$S$2
U2=SUM(T2:T155)
T3=R3*$S$2
T4=R4*$S$2

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Look up ISBLANK function

something like =IF(A1="","",calculate formula) to put a blank if there is nothing entered witht he cells that have the time.
 
Upvote 0
Thanks for the quick reply. Just so you know I am new to excel and formulas but I started playing around with the formula you have above and have gotten this for my first three time entries:

=IF(E2="","",(E2-W2)+IF(F2="","",(F2-W2)))+IF(G2="","",(G2-W2))

It works at adding the minutes but if I leave a cell empty I get this: #VALUE!

Where should I go from here?
 
Upvote 0
If the dates in Row 1 are actual dates, the following solution will determine the number of occurrences and the total number of late minutes. The number of minutes in Column R is formatted as [mm] and has a value that is a fraction of a day. The formula in Cell T2 multiplies the number of minutes by 24 to get the actual number of hours and fractions of an hour. Sheet1

*EFGHIJKLMNOPQRSTUVWX
12-Sep3-Sep4-Sep5-Sep6-Sep7-Sep8-Sep9-Sep10-Sep11-Sep12-Sep13-SepTotalMinutes LateLost
Production
Rate		Lost
Production
Value		Total Lost
Production		*Mon_Fri/
Start		Sat/
Start
27:35****6:50***8:05**36082.8082.8082.80*7:306:30

<colgroup><col style="width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 68px;"><col style="width: 68px;"><col style="width: 68px;"><col style="width: 68px;"><col style="width: 64px;"><col style="width: 87px;"><col style="width: 73px;"><col style="width: 74px;"><col style="width: 82px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
Q2=COUNTIF(E2:P2,">0")
R2=SUMPRODUCT(--(WEEKDAY($E$1:$P$1,2)<6),--(E2:P2>0),(E2:P2)-W2)+SUMPRODUCT(--(WEEKDAY($E$1:$P$1,2)=6),--(E2:P2>0),(E2:P2)-X2)
T2=R2*24*$S$2
U2=SUM(T2:T155)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
 
Upvote 0
stacyl14, your formula is trying to add a null string to a number which gives the error. You could make it work by making the value 0 if the cell is empty e.g.
=IF(E2="",0,(E2-W2)+IF(F2="",0,(F2-W2)))+IF(G2="",0,(G2-W2))
 
Upvote 0
MikeWx said:
If the dates in Row 1 are actual dates, the following solution will determine the number of occurrences and the total number of late minutes. The number of minutes in Column R is formatted as [mm] and has a value that is a fraction of a day. The formula in Cell T2 multiplies the number of minutes by 24 to get the actual number of hours and fractions of an hour. Sheet1

*EFGHIJKLMNOPQRSTUVWX
12-Sep3-Sep4-Sep5-Sep6-Sep7-Sep8-Sep9-Sep10-Sep11-Sep12-Sep13-SepTotalMinutes LateLost
Production
Rate		Lost
Production
Value		Total Lost
Production		*Mon_Fri/
Start		Sat/
Start
27:35****6:50***8:05**36082.8082.8082.80*7:306:30

<colgroup><col style="width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 68px;"><col style="width: 68px;"><col style="width: 68px;"><col style="width: 68px;"><col style="width: 64px;"><col style="width: 87px;"><col style="width: 73px;"><col style="width: 74px;"><col style="width: 82px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
Q2=COUNTIF(E2:P2,">0")
R2=SUMPRODUCT(--(WEEKDAY($E$1:$P$1,2)<6),--(E2:P2>0),(E2:P2)-W2)+SUMPRODUCT(--(WEEKDAY($E$1:$P$1,2)=6),--(E2:P2>0),(E2:P2)-X2)
T2=R2*24*$S$2
U2=SUM(T2:T155)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
Click to expand...

Hey!

Just wanted to know. When copying this data set into excel do I just highlight and ctrl+V it into the spreadsheet. Or is there a special paste function I need to use to apply it properly. I have Excel jeanie too but I really want to try out this formula and spread myself as I find it useful and inciting.

Many thanks!
 
Upvote 0
MikeWx,

Thanks, it is partially working now. Everything in row 1 is calculating properly except column "J", and rows "3" and below are not calculation properly. I copied and pasted your formulas from above, then copied them down to all the cells in those columns. But this is more than I have been able to do on my own, Thanks.
 
Upvote 0
Sorry, it's row 2 that is calculating correctly with the exception of column "J" which is a Saturday.
 
Upvote 0
Here's what I'm getting.

Excel 2010
DEFGHIJKLMNOPQRSTUVWX
1FIRST9/29/39/49/59/69/69/99/109/119/129/139/14TOTALMinutes LateLost Production RateLost Production ValueTotal Lost ProductionMon-Fri/ StartSat/ Start
2Steve6:451-4582.80-62.10$1,752.607:306:30
3Chris7:356:3528501,173.00
4Tom7:451465641.70

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
Q2=COUNTIF(E2:P2,">0")
R2=SUMPRODUCT(--(WEEKDAY($E$1:$P$1,2)<6),--(E2:P2>0),(E2:P2)-W2)+SUMPRODUCT(--(WEEKDAY($E$1:$P$1,2)=6),--(E2:P2>0),(E2:P2)-X2)
Q3=COUNTIF(E3:P3,">0")
R3=SUMPRODUCT(--(WEEKDAY($E$1:$P$1,2)<6),--(E3:P3>0),(E3:P3)-W3)+SUMPRODUCT(--(WEEKDAY($E$1:$P$1,2)=6),--(E3:P3>0),(E3:P3)-X3)
Q4=COUNTIF(E4:P4,">0")
R4=SUMPRODUCT(--(WEEKDAY($E$1:$P$1,2)<6),--(E4:P4>0),(E4:P4)-W4)+SUMPRODUCT(--(WEEKDAY($E$1:$P$1,2)=6),--(E4:P4>0),(E4:P4)-X4)
T2=R2*24*$S$2
U2=SUM(T2:T155)
T3=R3*24*$S$2
T4=R4*24*$S$2

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Column J will calculate correctly if cell J1 has Saturday's date in it. It should probably be 9/7/13. Since everyone uses the same start times in cells W2 and X2, change the formula in Cell R2 to the following, then copy it down; and see if that corrects the problem
=SUMPRODUCT(--(WEEKDAY($E$1:$P$1,2)<6),--(E2:P2>0),(E2:P2)- $W$2 )+SUMPRODUCT(--(WEEKDAY($E$1:$P$1,2)=6),--(E2:P2>0),(E2:P2)- $X$2 )
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
1K
phiero21
P
X
  • Solved
I need the latest update for Project Managers by last time stamp.
Replies
3
Views
277
ExceLoki
ExceLoki
J
  • Question
adding cells based on criteria across several columns
Replies
5
Views
113
Cubist
Cubist
K
  • Question
Production Time Calculator
Replies
4
Views
234
Drrellik
Drrellik
L
  • Solved
Formula for Calculating Production Finish Time
Replies
4
Views
276
Legacy 143009
L

Forum statistics

Threads
1,215,575
Messages
6,125,612
Members
449,238
Latest member
wcbyers

Share this page

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
Back
Top