+ Reply to Thread
Results 1 to 4 of 4

Conditional Formating of cells based on today's date, and another date

  1. #1
    Registered User
    Join Date
    08-02-2012
    Location
    NY, USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Conditional Formating of cells based on today's date, and another date

    Hello everyone ~ I'm using excel to track provider certifications and need some assistance in formatting a visual cue over a lot of data.

    Background:
    I use excel to track paramedics to see that they have 4 hours of medical control per year throughout their 3-year certification process. This would be easy to do if everyone were on the same annual cycle, but they are not. Provider certification cycles vary by month throughout the three years based on when they initially certified. I have about 100 paramedics to follow, each on their own different recertification cycle. It is difficult to see what year an individual is in, whether they have met their required hours, or how many hours they have left to do.

    I use a single summary sheet to track providers, and each has their own separate sheet that feeds the summary sheet. I would love to see the current year a paramedic is in as a highlighted cell, based on (today’s date). That way I could see what year they are in, and if they met the necessary hours for this year.

    Attached is an example, where I have used conditional formatting to cue how soon until a provider must recertify, and text color formatting based on the value in the cell. However, I cannot figure out how to highlight a cell in the MD column based on what year of the cycle they are actually in.

    For example, I would like to highlight E3 to show that Smith is in his third year, D4 to show that Adams is in his second year, C7 to show that Cook is in his first year, (based on today’s date) etc... I hope I am explaining this well enough!

    Thank you much!

    David
    CME File Example.xlsx

  2. #2
    Registered User
    Join Date
    08-17-2012
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Conditional Formating of cells based on today's date, and another date

    Hi David, have you tried using data bars? Conditional Formatting > Data Bars > More Rules > Format cells that contain... Then just format cells that have values greater than zero?

  3. #3
    Registered User
    Join Date
    08-02-2012
    Location
    NY, USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Conditional Formating of cells based on today's date, and another date

    I do like the look visually of the data bars, however I would like to highlight the cell of what year the person is in at this moment (their 1st, 2nd or 3rd) so that I could glance at a large group and see what they need. At the moment I have to figure out each row based on the expiration date and today's date to figure out that it's ok to have zeros in a column instead of other numbers.

    I have tried conditional formating with the following formulas, but it doesn't seem to work past the first or second column. I placed each rule in order and then to stop if true:
    =(C16-TODAY())<=365 to highlight cell blue showing the third year
    =(C16-TODAY())<=730 to highlight cell green showing the second year
    =(C16-TODAY())<=1095 to highlight cell red showing the first year

    This way, I could look at the blue colored cells to see who has accomlished what hours for this year's period, but based on their expiration date.

  4. #4
    Registered User
    Join Date
    08-02-2012
    Location
    NY, USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Conditional Formating of cells based on today's date, and another date

    I apologize... referring to the previous email and the previously attached example, the cell source would not be C16, but the corresponding B2 through B9 depending on the row. I had copied the formula from the original file.

+ 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.05739 seconds
  • Memory Usage 8,892KB
  • Queries Executed 15 (?)
More Information
Template Usage (31):
  • (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)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (4)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (4)postbit_legacy
  • (4)postbit_onlinestatus
  • (4)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 (46):
  • 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
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1