Get Custom Week Number from Date

[slinky123]

Hi,

My firm works in weeks that starts from Saturday each year. Week 1 started 31/03/2018 last year.

Some examples...02/04/2018 is week 1, 24/05/2018 is week 7,  08/02/2019 is week 45 and 09/02/2019 is week 46.

How do I convert any given date into the relevant week based on the above week numbering system?

Many thanks in advance,

Scott

[JoBen]

Hi @slinky123, looking at your examples below, the first Saturday in April in 2018 was on April 7th. so would April 2nd be in week 1 or week 52? 

[slinky123]

April 2 would be week 1. Apologies for any confusion!

[JoBen]

Okay. Try this formula where [Field1] is the date that you are using. IIF([Field1]>=DateTimeFormat([Field1],"%Y")+"-04-01", CEIL(DateTimeDiff([Field1],DateTimeFormat([Field1],"%Y")+"-04-01", "days")/7), CEIL(DateTimeDiff([Field1],DateTimeFormat(DateTimeAdd([Field1],-1,"Years"),"%Y")+"-04-01", "days")/7))

These are the results that I received using your examples below. 

[slinky123]

Hi,

That doesn't work as our week starts on a Saturday.

So 08-02-2019 is week 45 and 09-02-2019 is week 46...

[slinky123]

Think I've fathomed it based on your example...

This works:

IIF([Date]>=DateTimeFormat([Date],"%Y")+"-03-30", CEIL(DateTimeDiff([Date],DateTimeFormat([Date],"%Y")+"-03-30", "days")/7), CEIL(DateTimeDiff([Date],DateTimeFormat(DateTimeAdd([Date],-1,"Years"),"%Y")+"-03-30", "days")/7))

Thanks a lot for your help! Much appreciated!!

[JoBen · Accepted solution]

Okay. You bet!