-2

I'm looking at the in-app purchase report from the Windows Phone store at weekly aggregation level. Instead of date ranges, they're using week designations like "2015-W10" - presuming they've counted all weeks in a year.

What's the algorithm for translating those into dates - are they using the first Sunday (or Monday?) in a year as a starting date? Are they using Jan 1 to Jan 7 as week 1 and so forth?

EDIT: also, which timezone are they using?

EDIT2: here's some data. I have the following sales recorded (UTC):

   2015-01-02 16:35:00.000
   2015-01-04 17:13:00.000
   2015-01-09 22:44:00.000
   2015-01-12 07:34:00.000

And the sales report shows 2 sales in 2015 week 1, 1 sale in weel 2 and 1 in week 3.

The only scheme consistent with that would be the 2nd week going from 5th to 11th. 5th is Monday. Maybe they do count by calendar weeks, starting from Monday. I guess the 1st week is truncated, then - from 1 to 4...

Seva Alekseyev
  • 59,826
  • 25
  • 160
  • 281

1 Answers1

0

Looks like the weeks go like this. Unless Jan 1 is a Monday, the first week of the year is from Jan 1 up to and including the next Sunday. The first Monday of the year is the start of week 2 (unless it happens to be Jan 1), and so forth.

Didn't get a chance to test the situation where Jan 1 is a Monday. The last year like that was 2007, before Windows Phone was around, and the next one will be 2018.

The Store report provides the week of the year like this: "2015-W13". If you want to analyse those reports in Excel, matching weeks against actual dates, here are the formulae for the starting and ending date of the week of the year, assuming said format:

=DATE(MID(F2,1,4),1,1)+IF(MID(F2,7,2)="1",0,(MID(F2,7,2)-2)*7+MOD(8-WEEKDAY(DATE(MID(F2,1,4),1,1)),7)+1)

and

=DATE(MID(F2;1;4);1;1)+IF(MID(F2;7;2)="1";7-WEEKDAY(DATE(MID(F2;1;4);1;1));(MID(F2;7;2)-1)*7+MOD(8-WEEKDAY(DATE(MID(F2;1;4);1;1));7))

Replace F2 with the reference to the week of the year cell.

Seva Alekseyev
  • 59,826
  • 25
  • 160
  • 281