+ Reply to Thread
Results 1 to 5 of 5

Formatting of consecutive days worked

  1. #1
    Registered User
    Join Date
    04-18-2013
    Location
    Collierville, Tennessee
    MS-Off Ver
    Excel 2010
    Posts
    27

    Formatting of consecutive days worked

    I have a list of data for days worked for thousands of employees. I need to find and highlight each instance where, starting on a Sunday, the employee worked seven consecutive days in a row (Sun to Sat). I have the date columns A and B, the number "0" or "1" in the Column C, and the day of the week in the Column D, as shown below. To complicate matters, I only need to look at those situations where there is a "1" in the Column C next to the "SUN" - if there is a "0" I can ignore it.

    20131124 11/24/13 1 SUN
    20131125 11/25/13 0 MON
    20131126 11/26/13 0 TUE
    20131127 11/27/13 0 WED
    20131128 11/28/13 0 THU
    20131129 11/29/13 0 FRI
    20131130 11/30/13 1 SAT

    The above example meets the criteria. There is a "1" next to "SUN" and the employee worked SUN - SAT consecutively. At this point, I want to highlight the values in Column D (SUN-SAT) with a fill color (it doesn't matter which color). Attached is a sample file that shows more clearly what it would look like.

    Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Formatting of consecutive days worked

    You could make a new column and put this in the second row and drag it down. It will give you a 1 for the weeks you need to look at and a 0 if you don't.

    Please Login or Register  to view this content.
    Then you can do a conditional format in D1 like this:
    Please Login or Register  to view this content.
    Then copy D1, select the rest of the row and paste special formatting.
    Some people volunteer in soup kitchens or hospitals. I choose to make the world better by trying to help you with Excel. We're all learning.

    <---Click * Add Reputation for all helpful comments. It's like giving a smile.
    Forum Rules: How to mark your post [Solved] and have a happier, Excel enriched life.

  3. #3
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Formatting of consecutive days worked

    You could also put this as a conditional format for D2 and down as far as you need:
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    04-18-2013
    Location
    Collierville, Tennessee
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Formatting of consecutive days worked

    That worked perfectly! Thanks so much!

  5. #5
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Formatting of consecutive days worked

    Glad it worked for you. Thanks for the rep too.

+ 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] Average hours worked based on days worked during a pay cycle
    By jaycee66 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-14-2012, 06:27 PM
  2. Replies: 2
    Last Post: 11-05-2011, 07:29 AM
  3. looping not worked for consecutive data
    By isabella in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-14-2010, 04:39 AM
  4. Replies: 9
    Last Post: 09-03-2007, 08:26 PM
  5. consecutive weeks worked
    By kossdust in forum Excel General
    Replies: 6
    Last Post: 03-21-2007, 05:00 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