exceltimeweb-statistics

Splitting time series in 14-day slices (not specifically bi-weekly) in Excel

I have an excel workbook with a list of time series. Each record in this list contains a field with a date. So it goes like this:

+---------------------------------+
| A               | B             |
|-----------------|---------------|
| Mike Mikaelsson | Sep-30-2019   |
| John Jonson     | Oct-1-2019    |
| John Jonson     | Oct-1-2019    |
| Sarah Stewart   | Oct-1-2019    |
| John Jonson     | Oct-2-2019    |
| Sarah Stewart   | Oct-2-2019    |
| ...             | ...           |
| Dale Warner     | Oct-14-2019   |
+---------------------------------+

Shortly, it's a list that records some visits. I want to split this list into 14-day periods. Not specifically bi-weekly.

Currently, I've spotted the following solution. Provided that my date is found in B column, I add following formula in the C column:

=$B2-MOD($B2-2;14)+13

But I don't like this solution, because it doesn't seem reliable to me and, most importantly, it only works for bi-week periods.

What I get with this formula if I insert it in column C for the row with 30th of September? I get Oct-13-2019. If I drag cell down two populate the cells below, every cell of C column where B column lies between Sep-30-2019 and Oct-13-2019 will return Oct-13-2013. This is including the beginning Sep-30-2019 and Oct-13-2019 that ends the period.

It helps me to find the 14-day periods bi-weekly. By looking at column C I know that bi-week period starting with Sep-30-2019 will end on Oct-13-2019. So to select 14-day periods, I can scroll the list with dates down until the date in C changes to Oct-27-2019.

Cons: this only works for bi-week periods: it finds the beginning of the week (Monday in my regional standard) and its end 14 days later.

The question is: what would be the more elegant solution to select 14-day periods without sticking to Mondays or Sundays. Like if I select Oct-1-2019 as the start date, I would like to see how I can cut the 14-day slice with all records that end on Oct-14-2019 inclusively. Currently using bi-weekly splits I have to use dates that start on Modays, like Sep-30-2019 which comes on Monday.

Why do I need this after all? I want to know how many user visits I have in 14-day periods during the quarter. So as a first step I want to calculate the number of visits in 14-day slices.

Solution

  • In the end I did modify your formula. Here is my example data:

    enter image description here

    For the sake of clarity in the formula, I created two named ranges:

    "PeriodStart"   = G2
"DaysPerPeriod" = G3

    The formula itself works out the number of days between the current date (given in column B) and the start of all the periods (cell G2 or "PeriodStart") and calculates how many periods have elapsed since the start of all date periods.

    INT((B2-PeriodStart)/DaysPerPeriod) <== whole number of periods since the period start date

    Then, if you multiply that whole number of periods by the number of days per period AND add it to the start of all the periods, you get a date with the period boundary that moves with the date of your data. The following formula is used starting in cell B2 for all the data in the column.

    =PeriodStart+INT((B2-PeriodStart)/DaysPerPeriod)*DaysPerPeriod