+ Reply to Thread
Results 1 to 8 of 8

Conditional Formating - hide the minus symbol

  1. #1
    Registered User
    Join Date
    12-15-2017
    Location
    Cornwall, England
    MS-Off Ver
    2016
    Posts
    7

    Conditional Formating - hide the minus symbol

    Hi,

    I have a simple spreadsheet for tracking due dates and am using conditional formatting to add text around the cell contents. For example, if a cell contains 30, conditional formatting will display "Item Due in 30 days"

    The problem I am having is that when an item is overdue it is displaying -Item is overdue by 6 days. Is there anyway to hide the preceding minus symbol?

    It obviously needs to remain a negative number to show that it is overdue.

    I have tried formatting the cells using #;# which hides the minus symbol from the number itself but my conditional formatting ("OVERDUE by "0" Days") for any cell with a value less than 1 overrides this and shows the minus symbol.

    Can I integrate #;# into my conditional formatting of "OVERDUE by "0" Days"?

    Many thanks.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Conditional Formating - hide the minus symbol

    Set the text CF formulaically instead of via number formatting.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    12-15-2017
    Location
    Cornwall, England
    MS-Off Ver
    2016
    Posts
    7

    Re: Conditional Formating - hide the minus symbol

    Wow, you were quick!!

    Thanks for the suggestion.

    Is there a formula that would incorporate the calculation as part of a text output, all in the same cell? Or would this method require another column? I am trying to keep the number of columns to an absolute minimum.

    edit: Even if I add another column, the output still shows the minus symbol (see attached)
    Attached Files Attached Files
    Last edited by alreadytaken; 12-15-2017 at 09:11 AM.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Conditional Formating - hide the minus symbol

    Try:
    =IF(C2<30, "Overdue by " & ABS(C2) & " Days", "n/a")
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Conditional Formating - hide the minus symbol

    Here's one way:

    1) Change the formula in B2 to =ABS(A2-TODAY()) then drag the formula down column B.
    2) Select cell B2 > Conditional Formatting > Manage Rules > Change the red formula to =A1-TODAY()<1
    3) Change the yellow formula to refer to column A as well.

  6. #6
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Conditional Formating - hide the minus symbol

    You can use CF to control how positive/negative numbers display with text too.
    Select the range you want to format (col B) then enter the rules below, in the order given.

    Due in more than 90 days
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Cell format:
    Fill: none
    Custom number format: "Due in "#" days";;;@


    Due in less than 90 days
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Cell format:
    Fill: Orange
    Custom number format: "Due in "#" days";;;@


    Due today
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Cell format:
    Fill: Red
    Custom number format: ;;"Due today";@


    Overdue
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Cell format:
    Fill: Dark red
    Text: White
    Custom number format: ;"Overdue by "#" days";;@


    You could combine the last two into this:

    Due today or earlier
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Cell format:
    Fill: Dark Red
    Text: White
    Custom number format: ;"Overdue by "#" days";"Due today";@


    Explanation
    This works by using the Custom Number format structure, which is:
    Format for positive number ; format for negative number ; format for zero ; format for text
    See this page for more info: https://support.office.com/en-gb/art...2-09fab54be7f4


    Hope that helps - attached is a file showing it working in column E.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  7. #7
    Registered User
    Join Date
    12-15-2017
    Location
    Cornwall, England
    MS-Off Ver
    2016
    Posts
    7

    Re: Conditional Formating - hide the minus symbol

    A huge thank you to everyone who answered so quickly.

    Attached is my attempt at implementing all suggestions, apologies if I have misinterpreted any instructions.

    Unfortunately Glenn's suggestions of including ABS in the formula turned the negative number into a positive which meant that it didn't pick up the conditional formatting

    63falcon's suggestion worked an absolute treat, as did Aardigspook's.

    Thanks again for your collective genuis.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Conditional Formating - hide the minus symbol

    You're welcome, glad we could help and thanks for the feedback.

    As you're new to the forum, you may not know that you can add Reputation to anyone who's helped you by clicking 'Add Reputation' at the bottom-left of the relevant post. It would also be appreciated if you would mark the thread as Solved so that others know there's an answer here (assuming you're content that your problem has been solved). Thank you.

    Edit: thanks for the rep and marking the thread Solved.
    Last edited by Aardigspook; 12-22-2017 at 10:37 AM.

+ 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. Plus minus symbol
    By cecy_agr in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 10-02-2018, 09:39 PM
  2. Plus or Minus Symbol
    By Dowjd in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-20-2016, 04:38 PM
  3. [SOLVED] Conditional Formating Doesn't Work With # Symbol
    By ccpsc in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-19-2016, 01:02 AM
  4. Replies: 4
    Last Post: 01-23-2014, 10:14 AM
  5. [SOLVED] hide by conditional formating
    By nowfalapm in forum Excel General
    Replies: 3
    Last Post: 07-07-2012, 10:29 AM
  6. Minus (-) written as symbol
    By Lynx2x in forum Excel General
    Replies: 4
    Last Post: 03-07-2012, 08:26 AM
  7. hide cells based on conditional formating
    By djarcadian in forum Excel General
    Replies: 3
    Last Post: 04-01-2008, 09:43 PM

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