+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting Word and Numbers

  1. #1
    Registered User
    Join Date
    10-05-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    2

    Unhappy Conditional Formatting Word and Numbers

    Hi,

    So I have an sheet that has time in seconds which I converted to minutes and Seconds (so it is now text, not numbers). I am trying to have a conditioning formatting filter to have those whose time is greater then 19 minutes in row E be highlighted in red. Row E is a formula which takes the numbers from row D and converts it from seconds to minutes and Seconds as text. I have been trying to figure this out for the past hour and it has me confused! I've read all previous threads but still can't seem to figure it out! I've attached an example for further explanation.

    Also, In the actual spreadsheet I am using, I am using a SUMIF Formula is row D to pull the data. Is it possible to combine Row D and E's formulas in one cell so I don't have to hide row D when I figure out how to conditionally format the text.
    ROW D Formula: =SUMIF('Data Sheet'!$A:$A,Table511[@[Name]:[Name]],'Data Sheet'!G:G)
    Row E Formula: =TEXT(D2/86400,CHOOSE(MATCH(D2,{0,60,3600},1),"s ""sec""","m ""min"" s ""sec""","[h] ""hrs"" m ""min"" s ""sec"""))

    Any help would be awesome!!!!!
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Conditional Formatting Word and Numbers

    Why don't you use the original values in the conditional formatting?

    For example if you want to highlight rows in column E where the time is greater than 19 minutes try this.

    1 Select E2:E14.

    2 Goto Conditional formatting>New Rule...

    3 Select Use formula to determine cells to format...

    4 Enter this formula.

    =$D2>19*60

    5 Apply the required formatting.

    Note you can highlight the entire row if you select A2:H14 on the first step.
    If posting code please use code tags, see here.

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,598

    Re: Conditional Formatting Word and Numbers

    In columns D, just:
    SUMIF(...)/86400

    Then cutom format the columns with either
    m "min" s "sec"
    or,
    h " hrs " m "min" s "sec"
    Ben Van Johnson

  4. #4
    Registered User
    Join Date
    10-05-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Conditional Formatting Word and Numbers

    Thank you guys both for the help! I was able to get it into one cell and Conditionally format it!

+ 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. [SOLVED] Conditional formatting in excel/word
    By k11ngy in forum Word Formatting & General
    Replies: 3
    Last Post: 01-25-2017, 06:39 AM
  2. Conditional formatting for new excel on word
    By ZiDanno in forum Excel General
    Replies: 10
    Last Post: 03-10-2016, 03:54 PM
  3. Replies: 2
    Last Post: 10-30-2014, 01:49 AM
  4. [SOLVED] Conditional formatting - formatting the lowest 2 numbers in a list of 12 numbers
    By ktbb0312 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-09-2013, 09:59 AM
  5. Replies: 9
    Last Post: 03-26-2012, 02:16 PM
  6. Conditional Formatting - Numbers & Word
    By mbe23 in forum Excel General
    Replies: 2
    Last Post: 03-16-2011, 01:31 AM
  7. Conditional formatting using a trigger word
    By stevewilde in forum Excel General
    Replies: 1
    Last Post: 10-15-2009, 09:29 AM

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