Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Change Date Font Color

    I have cells formatted for date/time like this 2/21/2011 1404 where 1404 is the time of day. I would like to change the color of the font if the time is >= 1200, i.e. have all the p.m. date/times colored. The Right function finds the 4 time digits, so I assume I can us an IF(Right(A!,4) >= 1200, ???,) but is there a ??? function or command to change the font color?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can use conditional formatting. Excel stores dates/times in units of days so the integer component of date/time is the date (the number of days past Jan 1, 1900.) The decimal portion is fraction of a day 6AM is 6/24 = 0.25, Noon is 0.5, 6PM is 0.75, etc. Times that are PM all have decimal portions >0.5

    So you can use conditional formatting [Format - cells - conditional formatting...] to set the formula to use a formula like:
    =Mod(a1,1)>0.5

    Change A1 to whatever the activecell is when you set the conditional formatting and change the format in the conditional formatting dialogue box to whatever you want. [If you consider NOON to be PM (Technically noon and midnight are neither AM or PM), use >=0.5]

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks Steve. That works.

Posting Permissions

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