HELP! Adding 24 has ruined my formula....

sage123

New Member
Joined
Dec 2, 2018
Messages
13
Hi guys,

I seem to be having an issue getting excel to calculate properly.

I have a table showing: Picker name(Col B) the time spent picking (Col C), units picked per person (Col D) and want to work out a pick rate(Col F).

[TABLE="width: 465"]
<colgroup><col width="160" style="width: 120pt; mso-width-source: userset; mso-width-alt: 5851;"> <col width="157" style="width: 118pt; mso-width-source: userset; mso-width-alt: 5741;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <tbody>[TR]
[TD="width: 160, bgcolor: transparent"]B
[/TD]
[TD="width: 157, bgcolor: transparent"]C[/TD]
[TD="width: 99, bgcolor: transparent"]D[/TD]
[TD="width: 112, bgcolor: transparent"]E[/TD]
[TD="width: 92, bgcolor: transparent"]F [/TD]
[/TR]
[TR]
[TD]Picker[/TD]
[TD]Time spent picking[/TD]
[TD]Units Picked[/TD]
[TD]Rounds picked[/TD]
[TD]Pick rate[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]01:30[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


I have 2 of these tables for 2 different operations. One works correctly, the other doesn't.

Time spent picking is calculated from another tab using a start and finish time. The below formula is the one not working properly. I have to add 24 (hours) to the start time as the time duration occurs over midnight and without it I get #### errors.

=IF('Input Corby Picker Data'!$F4<'Input Corby Picker Data'!$E4,24+'Input Corby Picker Data'!$F4-'Input Corby Picker Data'!$E4,'Input Corby Picker Data'!$F4-'Input Corby Picker Data'!$E4)

I use the same formula without the 24 on another table and it works fine.

Calculation for pick rate is =IFERROR(D6/(C6*24),"")
This is the same for both tables.

I get the answer 0 rather than an error/reference/value message but this isn't right.

Snippet above has nothing in the units column but it is part of a larger table with other rows that have values in this column that should change the Pick Rate to more than 0.

Any help you could give me would be greatly appreciated! :)
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Change:
=IF('Input Corby Picker Data'!$F4<'Input Corby Picker Data'!$E4,
24+'Input Corby Picker Data'!$F4-'Input Corby Picker Data'!$E4,
'Input Corby Picker Data'!$F4-'Input Corby Picker Data'!$E4)
to:
Rich (BB code):
=IF('Input Corby Picker Data'!$F4<'Input Corby Picker Data'!$E4,
1+'Input Corby Picker Data'!$F4-'Input Corby Picker Data'!$E4,
'Input Corby Picker Data'!$F4-'Input Corby Picker Data'!$E4)

Or:

Rich (BB code):
=MOD('Input Corby Picker Data'!$F4-'Input Corby Picker Data'!$E4,1)
 
Upvote 0
if for any reason you get more than 24 hours it will look wrong again hence the square brackets

the Integer are days, the decimal portion is the time values h:mm:ss etc
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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