+ Reply to Thread
Results 1 to 6 of 6

Conditional Formatting: Highlight a cell containing a date after 1 year past that date!

  1. #1
    Registered User
    Join Date
    01-02-2012
    Location
    Denver, CO
    MS-Off Ver
    Excel 2007
    Posts
    17

    Conditional Formatting: Highlight a cell containing a date after 1 year past that date!

    Hi all,

    I am having a small excel problem, and I was hoping that the wonderful Excel Forum community could help me out?

    I maintain an employee training database, and Column D contains "Date Trained".

    All I need to do is set a conditional formatting on Column D that highlights the date trained (Red) once a year has passed.

    The only caveat that is making this difficult is that the dates are mixed on the spreadsheet, which may/may not make this more difficult to accomplish.

    Ex.

    Contreras 4/12/2012
    Duran 4/12/2010
    Gamble 8/10/2012
    Gyimah 12/12/2011
    Hale 8/10/2012
    Jaimes 4/12/2011

    etc.

    I get the feeling that it's a simple solution, but I haven't been able to figure it out all day!

    Any help is greatly appreciated. Thanks so much in advance

    -Chris
    Last edited by Christopher1990; 08-22-2012 at 03:19 PM.

  2. #2
    Registered User
    Join Date
    01-24-2011
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    74

    Re: Conditional Formatting: Highlight a cell containing a date after 1 year past that date

    Select the column, then under Conditional Formatting, set it to Format Only Cells With:

    Cell Value Less Than =TODAY()-365

    then set the Font to Red - or fill to green, or whatever you want.





    if this works for you, please Edit your original post, change the header to "[SOLVED]" and check the star in my reply if so inclined.
    thanks,
    Attached Images Attached Images
    Last edited by Hang Glider; 08-22-2012 at 01:54 PM.

  3. #3
    Registered User
    Join Date
    01-02-2012
    Location
    Denver, CO
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Conditional Formatting: Highlight a cell containing a date after 1 year past that date

    That worked well, with the exception that i have some blanks in the "Date Trained" column that automatically turn red. How can I get excel to ignore those and only highlight the dates 1 year due?
    Last edited by Cutter; 08-22-2012 at 02:58 PM. Reason: Removed whole post quote

  4. #4
    Registered User
    Join Date
    01-24-2011
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    74

    Re: Conditional Formatting: Highlight a cell containing a date after 1 year past that date

    I'll look, but quickest and easiest is to format Font, not Fill. If it is blank, there's no Font to format, but there is always a box to fill...

  5. #5
    Registered User
    Join Date
    01-02-2012
    Location
    Denver, CO
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Conditional Formatting: Highlight a cell containing a date after 1 year past that date

    Quote Originally Posted by Hang Glider View Post
    I'll look, but quickest and easiest is to format Font, not Fill. If it is blank, there's no Font to format, but there is always a box to fill...
    I thought about doing that too, but my boss wanted the boxes to highlight as red or yellow. My hands are tied there

  6. #6
    Registered User
    Join Date
    01-24-2011
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    74

    Re: Conditional Formatting: Highlight a cell containing a date after 1 year past that date

    played with that for an hour or so...no luck. perhaps someone else can chime in, I'll be curious to see an answer.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.06140 seconds
  • Memory Usage 8,955KB
  • Queries Executed 15 (?)
More Information
Template Usage (33):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (6)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (6)postbit_legacy
  • (6)postbit_onlinestatus
  • (6)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (47):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1