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

Options
✭✭

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

Tags:

• ✭✭✭✭✭✭
Options

Try something like this...

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

• ✭✭
edited 07/13/22
Options

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.

• ✭✭✭✭✭✭
Options

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)

• ✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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?

• ✭✭
Options

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!

• ✭✭
Options
• ✭✭✭✭✭✭
Options

Happy to help. 👍️