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

[sage123]

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).

B	C	D	E	F
Picker	Time spent picking	Units Picked	Rounds picked	Pick rate
P	01:30	0	0	0

<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> </tbody>

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!

 

[mole999]

do you custom format as [h]:mm ?

 

[Phuoc]

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:

=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:

=MOD('Input Corby Picker Data'!$F4-'Input Corby Picker Data'!$E4,1)

 

[sage123]

Its in hh:mm Mole. Your new formula works perfectly thank you Phuoc!

 

[mole999]

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