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
Solved! Go to Solution.
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?
April 2 would be week 1. Apologies for any confusion!
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.
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...
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!!
Okay. You bet!