Overlapping date ranges, part I

  • Jul. 11, 2014
Example files with this article:
  • Soccer and exams

    • Introduction



    For all those soccer fans out there… we are all excited to see what team is going to win the cup! Tomorrow we will experience the final of what proved to be an interesting championship. In this article (the first of 2 articles on the same topic and example file) I will write about overlapping date ranges, taking the soccer World Cup as a practical case. In fact, many students face difficult dilemma's these days: studying or watching soccer? :-) In Belgium, the largest part of the month of June is dedicated for exams at universities and other teaching institutions. But also during June, there are a good number of matches at the World Cup soccer. Exam periods can differ from year to year, and the World Cup calendar schedule can differ. For instance, during odd years, there is no major tournament for soccer. In 2 years, we will have the European Championship soccer in France, again leading to conflicts in the agenda's of many people.

    The current article and spreadsheet shows a neat way to present the periods (soccer and exams) in a graphical way using Conditional formatting. In doing this, we will definitely need some date functions in Excel to calculate periods and overlaps.

    Conditional formatting

    So, let us have a look at the overlap between exam periods and soccer tournaments. Exam periods are defined (by myself) as starting at the first Monday in June, and lasting for 4 full weeks, except the last Sunday - effectively stopping on the 4th Saturday after the start of the period. I guess this is good enough as an approximation, we do not need to have the exact dates for the illustration. In fact, you can simply overtype some cells in the spreadsheet and have a different period. Or you can change the the topic "exams" for something else that makes more sense to you. The other date range is each big soccer tournament that is held every 2 years: European Championship when the year can be divided by 4, the World cup in the other even years. In odd years, no such major tournament is held. For tournaments in the past, we know the dates when the teams played their matches, but for future tournaments, the exact dates are not known yet. I will put question marks in that case.

      In the picture above you can see 3 colors, which are the graphical result of some calculations:
    1. Red is the worst period: exams and no soccer tournament ! The abundance of red is explained not in the least way by the odd years of course.
    2. Orange is somewhat better: exams but soccer at the same time
    3. Green is the best of all cases: soccer without exams !
    4. Finally, white cells mean: no soccer and no exams

    Furthermore, I added a graphical effect for the weeks in June and July. The vertical bars mark the borders of a week (Monday to Sunday). This is done with conditional formatting too. As each cell in the range from June 1 to July 31 contains a formula (that I will certainly explain to you), we can format the cells based on the result of the formula. As Excel 2007 and up allow considerably more options for conditional formatting compared to Excel 2003 and earlier, you can use the file only in the later versions of Excel. My cells can satisfy up to 8 conditions at the same time so the 3 conditions that we had in Excel 2003 will be way too little! Luckily I am now on Excel 2013.

    In this article, I propose to discuss the formulas. They are not overly complicated, but they aren't simple either. Looking at the (downloadable) file, we see:

    After the years in column A, we have a first part in yellow on the exams, and a second part in grey on soccer tournaments. Note that the words EXAMS and SOCCER are not in cells that have been merged, I hate merged cells wholeheartedly :-) The same visual effect is presented with the Alignment "Center across selection". Much better in my opinion. For exams, we have the following formulas:

    1. Column B: The first of June for that year. Easy, the DATE function.
    2. Column C: The date of the first Monday in June. This more tricky. We take the previous cell in column B (June 1) and add a number of days to it. We use the WEEKDAY function to see what day in the week (1 to 7) that first of June is/was/will be. If it's a Friday for example, we add 3 to make up for Saturday, Sunday, Monday. Likewise for the other days in the week. If June 1 happens to be a Monday, we should add no days, instead of 7 days until Monday next week. Therefore, the MOD function takes the remainder when dividing by 7.
    3. Column D: 4 weeks later. We add 4 full weeks of 7 days and would end up on a Monday again, but from that we subtract 2 days to end up the Saturday before.
    4. Column E: duration. Easy for the formula, but have a look at the cell's numberformatting.

    For the next part, we have soccer.

    1. Column F: Championship. A formula based on what I wrote about dividing even years by 4 or not. The function ISODD might not be known to all of you, this is just a simpler formula than checking whether the MOD (modulo) when dividing by 2 leaves 1 or not.
    2. Column G to J: data input.
    3. Column K: an interesting formula, in that the N function is used to convert a text like "?" to 0. Numbers will stay numbers when supplied to the N function. The MIN function calculates the minimum of (here) 2 numbers.

    Filtering some columns

    As a side remark, have a look at columns A, F, I, J for row 2. You will notice the dropdown arrows for the Autofilter. By default, if you turn on the Autofilter, each column will have a dropdown arrow. There is a way to hide them for columns where you do not want the dropdown arrows to appear. That is good news! The bad news is that you need a bit of VBA-coding to do it. But the bad news is converted to good news again since I provide you with the codes needed.

    VBA procedure

    Here is my code: 

    Sub SelectiveAutofilterArrows()
    

    ' Wim Gielis
' https://www.wimgielis.com
    

    '''''
' VBA-code to only show the Autofilter dropdown arrows on certain columns
' 07/09/2014
'''''
    
    Dim c As Range

    Application.ScreenUpdating = False

    For Each c In Intersect(ActiveSheet.UsedRange, Rows(3))

        'Option 1
        c.AutoFilter Field:=c.Column, _
                     Visibledropdown:=InStr("-1-6-9-10-", "-" & c.Column & "-")

        'Option 2
        'c.AutoFilter Field:=c.Column, _
                      Visibledropdown:=(c.Column = 1) + (c.Column = 6) + (c.Column = 9) + (c.Column = 10)

        'Option 3 - Excel 2013 (and up) only
        'c.AutoFilter Field:=c.Column, _
                      Visibledropdown:=WorksheetFunction.Xor(c.Column = 1, c.Column = 6, c.Column = 9, c.Column = 10)

    Next

    End Sub

    In the code, I make a loop over cells in row 3, where the cells are part of the UsedRange of the worksheet. For each cell, I test the .Column and take action. The action is hiding the Autofilter dropdown arrow. My preferred option is Option 1, the other options are left there to inspire you. Not all options are equally long in terms of coding. Please experiment with the given solutions, if you have Excel 2013 you can also make use of the Xor function, a nice recent addition to the set of existing Excel formulas.

    Let me finish this article by directing you to the second part of this article: part II on explaining the conditional formatting, a must-read if you want to increase your knowledge about conditional formatting!




    Homepage

    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links