+ 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
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,164

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  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... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Conditional Formating - hide the minus symbol

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



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

    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 (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    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

    Recently moved house, internationally, during COVID (!) 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 (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    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