+ Reply to Thread
Results 1 to 5 of 5

Conditional Format: Data bar Percentage of time string ("h" "m")

  1. #1
    Registered User
    Join Date
    07-21-2012
    Location
    Ames Iowa
    MS-Off Ver
    Excel 2007
    Posts
    3

    Conditional Format: Data bar Percentage of time string ("h" "m")

    I have two columns of data (time in a format that shows hours and sometimes minutes... ie "20h 32m" or maybe "24.5h"). The first column is the estimate of time required and the second is the remaining time.

    OE Remaining
    24h 17h 20m
    16h 16h
    40h 28.5h


    What I would like to do, is add conditional formatting to the column showing the remaining time... with a data bar (remaining time as a percentage of the original estimate). Because of the nature of my data (table that is reloaded), I want to have the conditional formatting in place (in the cell with the text).

    My first challenge is this strange time format. To conquer that, I thought maybe I could apply a conversion like this:
    =IFERROR((VALUE(LEFT(J11,FIND("h",J11)-1)))+VALUE(MID(J11,FIND("h",J11)+1,FIND("m",J11)-FIND("h",J11)-1))/60,(VALUE(LEFT(J11,FIND("h",J11)-1))))

    this formula will take a cell with a value like "17h 20m" and convert it to "17.333". So, that works fine. I could create two new columns that store this information.

    My question - is it possible to show a progress bar in my remaining time column using conditional formatting (a percentage of the original estimate)?

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Conditional Format: Data bar Percentage of time string ("h" "m")

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you

  3. #3
    Registered User
    Join Date
    07-21-2012
    Location
    Ames Iowa
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Conditional Format: Data bar Percentage of time string ("h" "m")

    Here's the spreadsheet explaining the question.
    Attached Files Attached Files

  4. #4
    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
    80,775

    Re: Conditional Format: Data bar Percentage of time string ("h" "m")

    This can't be done, I'm afraid. Data bars will only work on numbers that exist in the cell itself. You can't use a formula in a data bar CF rule.

    I don't know if it's possible to do it with VBA.
    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.

  5. #5
    Registered User
    Join Date
    07-21-2012
    Location
    Ames Iowa
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Conditional Format: Data bar Percentage of time string ("h" "m")

    Afraid of that. thanks!

+ 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] Brief formula to convert time values to "shift1", "shift2", "shift3".
    By darekpawel in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 04-11-2019, 07:05 AM
  2. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  3. Replies: 2
    Last Post: 07-13-2018, 11:52 PM
  4. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  5. [SOLVED] formula help: conditional format of one column based on "YES"/"NO" content of four others!
    By 10 Dollar Bill in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-20-2014, 01:59 PM
  6. Replies: 1
    Last Post: 08-15-2014, 06:00 AM
  7. Replies: 2
    Last Post: 11-01-2012, 04:35 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