WEEKNUM() With a Different Starting Date?

[Anton_Dickens]

Hi everyone!

I wonder if there is a simple way to do this...

Is there any way to manipulate WEEKNUM() so that week 1 begins on a different date, but would not be influenced by the cross over of calendar year when returning the results?

The reason is that I want to select data starting at the start of our financial year, not the calendar year. So W1 would start on 1 April each year.

I am doing this from a virtual column selecting records and summing a particular column from a different table, so I am going to experience a problem with the crossover between years. 

Otherwise, I guess it's the long road of dreaming up a workaround...

Thank you in advance!

Anton

[Suvrutt_Gurjar]

In general, you are correct that because of year rollover, it could be a complex expression.

One option, if there are not multiple years and just handful of years ( say current year and previous year) , you may want to consider using a lookup table of dates and week numbers. You could in turn get the week number for the dates in app by using lookup function on that table Not ideal but may be worth exploring.

Other community colleagues may have better suggestions.

[Anton_Dickens]

Thanks Suvrutt, that got my brain engaged!

I ended up getting a result, but will require an annual adjustment made to the formulas which isn't ideal, I would love to have it totally automatically calculating though!

I did the following (would love some feedback/pointers):

• In the Timesheet (main data table) table, created a virtual column which returns a result using an IF statement by classifying each record into a financial year (so for 2022 calendar year any records with a MONTH() result of <4 becomes "2021" while the rest become "2022") - so "2022" is just a name, covering from 01/04/22 to 31/03/23.
•  In the Timesheet (main data table) table, created another virtual column which returns a result of an adjusted week number using an IF statement, so any records with MONTH() <4 has one correction applied (in this years' case +40), and the rest a separate correction (in this years' case, -13)
• Then in the table where these are being compiled, each VC for each week selects and sums based on the adjusted week number, according to what year each row has set and the person whose timesheet will be expressed in this row (we are comparing output here)

So annually I need to work out the corrections and alter the formula of the adjusted week number. We aren't comparing year against year at this stage, but that will need further work if it's decided that we need it (a separate "adjustments" table maybe...)

Thanks again!

Anton

[Joseph_Seddik]

1. someDate
     
2. Date of aprilFirst of the year of someDate:
   EOMONTH([someDate], 3 - MONTH([someDate])) + 1
     
3. lastDayYear-1, this is Last day of the previous year of someDate:
   EOMONTH([someDate], -1 * MONTH([someDate]))
     
4. someDateWeek:
   1 + ISOWEEKNUM([someDate]) - ISOWEEKNUM([aprilFirst])
   + IF([someDate] < [aprilFirst], ISOWEEKNUM([lastDayYear-1]), 0) 

[Anton_Dickens]

Hi Joseph,

Thank you very much for this, very clever!

I run these through and the results look good for most of the records. However from some dates from late 2019 through to 31/04/20 it's returning a combination of negative results, zero results and positive results which aren't correct - do you know at a quick glance why that would be? If not it's okay, I'm just not familiar with these expressions and would take a bit of working out to understand them. I'm not worried about these results, but whether there is some condition that would return similar results in future years?

Thank you again!

Anton

[Joseph_Seddik]

Hello Anton,

Sorry for late reply, I've been away from the forum. Would you please show some examples of those results in the app; so that we could better know what's happening. 

At first glance, possibly this part:

+ IF([someDate] < [aprilFirst], 

should better be:

+ IF(MONTH([someDate]) < MONTH([aprilFirst]), 

[Anton_Dickens]

Hi there Joseph,

No worries, thanks for the reply.

Below is from the test for a line where the date is 16 January 2020 (to avoid confusion on date layouts are we use DD/MM/YYYY here). I tried the change you suggested which gives the similar result.

The problem is evident from dates from the first ISO week of 2020 (so includes 30&31 Dec) as Week -12 and increases up through zero, then produces week 1 as the last full week in March (not the week running into April), but is corrected when you get into April (we had COVID lockdown here at that time so we don't have any entries at the start of April unfortunately).

But after this point, the formula works great, it is just the rows between 30/12/19 - 30/3/20. It's almost like recognising a year which doesn't end in 2- has thrown it off.

Thank you!

Anton