Displaying zero instead of #VALUE!

suxrule

New Member
Joined
Mar 2, 2021
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
Hi,

I'm trying to get my #VALUE! error to just display zero.

I've included 3 examples of whats happening with the formula I'm using.

The two red cells are obviously not what I want to happen and the green one is my desired result.

My problem is #VALUE! is displayed when I enter anything other than a number, I'd just like to display zero. You'll see what I've tried in B4 is to use IFERROR but to be honest I'm not 100% sure what I'm doing, I'm just searching the internet and trying to figure it out as I go.

Basically I want ((end time - start time) - break time)

(I've just spent about half an hour trying to get the Mini-Sheet working on a Mac and I'm getting no where so it's pictures I'm afraid, sorry)

Any help much appreciated.

Full.png


B4.png


E4.png


Desired output (but display zero if anything other numbers are entered)
H4.png
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

mamady

Board Regular
Joined
Sep 23, 2011
Messages
238
Office Version
  1. 365
Platform
  1. Windows
You were close. Please try the following formula:

=IFERROR(((I3-H3+(I3<H3))*24)-J3,0)

Regards
 
Solution

suxrule

New Member
Joined
Mar 2, 2021
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
Thanks so much!

I've asked 2 questions so far on here and I'm sure both are very simple to most but I've been met with nothing but polite help.

Other platforms can be a bit judgy when you ask a simple question (StackOverFlow for example 😒) anyway thanks again.
 

mamady

Board Regular
Joined
Sep 23, 2011
Messages
238
Office Version
  1. 365
Platform
  1. Windows
Thanks so much!

I've asked 2 questions so far on here and I'm sure both are very simple to most but I've been met with nothing but polite help.

Other platforms can be a bit judgy when you ask a simple question (StackOverFlow for example 😒) anyway thanks again.

it is my pleasure to help :)

Regards
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,697
Messages
5,765,989
Members
425,321
Latest member
stefanov07

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