Fiscal Week (in a Quarter) based on a Date Formula

Natasa5

Hi,

I am struggling putting together a forumla that would calculate Fiscal Week (13 FW in a FQ) based on a date and taking in consideration that our FY starts February 1. Any help/advice would be appreciated!

Nataša

Paul Newcome

Try something like this...

=INT(([Date Column Name]@row - DATE(YEAR([Date Column Name]@row), 02, 01)) / 13)

Natasa5

Hi Paul,

The formula works, but I am getting undesired results, i.e. week 17 for August 20, which is week 4 of Q3. Any idea? Thank you.

Paul Newcome

If the FY start is 1 Feb then 20 Aug is 29 weeks into the FY. That means week 3 of Q3. This formula should output that for you...

=MOD(INT((Date@row - DATE(YEAR(Date@row), 2, 1)) / 7) + 1, 13)

Natasa5

Hi Paul,

Thank you for your help. I tried the new formula, but it also does not show correct FW.

Our fiscal weeks start on Saturday, so I guess that is why for us Aug 20 is the first day of W4 as for you is W3. And As you can see there are results with W0. Can these details be fixed? Thank you.

Paul Newcome

Yes. Feb 1 is a Tuesday, so I assumed you were starting on Feb 1. In your case, the first day of the first fiscal week is Jan 29 correct?

Natasa5

Hi Paul, yes, that is correct; the first day of Q1 22 was Jan 29. When I added this date to the formula I got the right numbers. Thank you for baring with me and your help. Much appreciated!

Natasa5

Paul Newcome

Happy to help. 👍️