+ Reply to Thread
Results 1 to 3 of 3

Why doesn't IFERROR work with negative time values?

  1. #1
    Registered User
    Join Date
    01-26-2022
    Location
    Wrightstown, New Jersey
    MS-Off Ver
    Windows 10 Enterprise, 1909
    Posts
    20

    Why doesn't IFERROR work with negative time values?

    I created a timestamp grid to track start/stop times and cumulative times for a set of projects. But when I put in the start time without the end time, I get the hashtag string because it's a negative time value. This throws some people off so I tried to use IFERROR, but that doesn't override the hashtag string. Is there any way to leave the cell blank until the finish time is entered?
    Attached Files Attached Files
    Last edited by smhelgerson49; 01-26-2022 at 12:00 PM. Reason: Solved!

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,575

    Re: Why doesn't IFERROR work with negative time values?

    Because a "string of hashtags" is not a formula error. It is number formatting "error" that simply means Excel cannot display the resulting number using the current number format. The underlying cell value is still a valid number and not an error.

    You probably need a more robust number format. Something that will ignore negative values. Try something like "hh:mm;;hh:mm;@" where the first section tells Excel how to display positive values (as time of day), the second (empty) section tells Excel how to display negative values (empty meaning don't display), the third section tells Excel how to display 0 values (as time of day); and the fourth section tells Excel how to display text (@ means to simply display the text).

    FWIW, when I was first learning spreadsheets, this was one of my most frequently referenced help files: https://support.microsoft.com/en-us/...7-9c9354dd99f5
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    01-26-2022
    Location
    Wrightstown, New Jersey
    MS-Off Ver
    Windows 10 Enterprise, 1909
    Posts
    20

    Re: Why doesn't IFERROR work with negative time values?

    That's brilliant!!
    Thank you.
    I also took out the third value so that a time value only shows if there is a start and finish time entered. Very slick, indeed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Large function doesn't work with negative numbers
    By srdavisgb in forum Excel General
    Replies: 1
    Last Post: 07-01-2019, 02:57 PM
  2. [SOLVED] IFERROR function doesn't seem to work
    By joel.mugabe in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-08-2016, 06:48 AM
  3. xlfn.IFERROR or IFERROR don't work in Excel 98-2003
    By dj_danu01 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-26-2013, 02:53 AM
  4. [SOLVED] Subtracting text boxes from one another doesn't work if answer is negative.
    By colvinb in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 07-19-2012, 06:26 AM
  5. Consolidating Rows Doesn't Include Negative Values
    By aferoz in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-16-2012, 11:07 AM
  6. vba doesn't work with "iferror()" and formula autofill problem
    By lkim in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-10-2012, 07:47 AM
  7. Brackets are negative-sum doesn't work?
    By weaver00 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-28-2008, 09:09 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1