+ Reply to Thread
Results 1 to 5 of 5

multiple date ranges & conditional formatting

  1. #1
    Registered User
    Join Date
    07-07-2010
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    15

    multiple date ranges & conditional formatting

    I have a list of transaction rows on a spreadsheet. The column to the far left contains dates for each transaction.

    On another spreadsheet I have a matrix of date intervalls like (one cell for each date):
    2010-04-02 2010-06-03
    2010-03-02 2010-06-02

    Is it possible to create a conditional formatting that evaluates on the matrix of date intervalls? I.e. if a date in my list of transactions is found in the date ranges specified, then change its formatting?

    Thanks!
    Erik

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: multiple date ranges & conditional formatting

    Create named ranges for the date intervals of start_dates and end_dates and use a CF ormula of

    =SUMPRODUCT(--(start_dates<=A2),--(end_dates>=A2))

  3. #3
    Registered User
    Join Date
    07-07-2010
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    15

    [SOLVED] Re: multiple date ranges & conditional formatting

    This is beautiful! Thanks a lot Bob!! =)

    Just a short note for Excel 2007 and 2010 users, I had to change "," to a ";".

  4. #4
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: [SOLVED] Re: multiple date ranges & conditional formatting

    No, that is not because of Excel 2007 or 2010, it is because of your Excel version. You have continental settings, the Brits and Yanks amongst us would still use commas.

  5. #5
    Registered User
    Join Date
    07-07-2010
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: multiple date ranges & conditional formatting

    Good to know! Thanks!

+ 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1