1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel: Format rows based on date and a few other specifics

Discussion in 'Business Applications' started by Tekon, Jun 13, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. Tekon

    Tekon Thread Starter

    Joined:
    May 10, 2013
    Messages:
    10
    Hi all,

    I would like to conditionally format the attached spreadsheet so that each alternating week is shaded on whole set of rows.

    Also with our roster the week runs Thurs to Wed.

    I've looked quite a bit into conditional formatting now. But this one has me stumped.

    Thanks for any advice.
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Hi, I think you can use the conditional formatting but will have to do this using a formula.
    The function in Excel is WEEKNUM(date, weekday)
    In your case that would be WEEKNUM(value in Column A, vbThursday)
    All you need to do is sya if the weeknum(x,vbthursday) = and even number format that row in that coloer else no formatting.
    That way whenever the week nr changes the formatting changes.
    If your dates are sorted in ascending the formatting will show for only even weeknumbers.
    I hope my explanation helps a littel.
    You can see the resul if you place the formual in Column G2=WEEKNUM(A2;14) and the 14 stands for Thursday
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    The conditinal formatting is quite a P in the A but the idea works

    You might not like the colour but . . . :)
     

    Attached Files:

  4. Tekon

    Tekon Thread Starter

    Joined:
    May 10, 2013
    Messages:
    10
    Thank you Hans,

    That formatting looks fine. However the formula you've applied seems to work only for the range A2 to A14 covering the rows with data already entered

    I was looking for the format to be applied as more entries are made. Is this possible? Can the formula be used for all of column A?

    Tekon. (Tekko)
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Well, I just showed you a possible solution.
    If you select the cells in row 2 and open the conditional format and edit the rule you will notice that the applied range is set, it tells you Applies to $A2:$F$14 well, change this to the range you require, you can even set it to $A2$F$100 but then just might have to change the formula to only apply if if the value in column A is > 0

    The formula will be: =IF($A2>0,INT(WEEKNUM($A2;14) / 2)=WEEKNUM($A2;14) / 2)
     
  6. Tekon

    Tekon Thread Starter

    Joined:
    May 10, 2013
    Messages:
    10
    Thanks again. Will have a play with it and see how I go.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1101162

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice