How to auto-populate my company's fiscal year week number

Diego Casillas Zaragoza

Hello,

I am having a formula in the "number of chambers shipped" to add them from a different sheet.

the "date" column will auto-populate

I would like for the Week column to auto-populate my company's fiscal year. I have no idea how to do that. Can someone help me figure this out?

Mark Cronk

Hi Diego,

If your fiscal year starts on January 1st you can use =weeknumber(date@row)

If your fiscal year is not January 1st you'll need a different formula. If your fiscal years starts on October 1st the formula would be:

=ROUND(((Date@row - DATE(2021, 10, 1)) / 7) + 0.49, 0)

Substitute the 1st day of your fiscal year for DATE(2021, 10, 1)

Work for you?

Mark

Mark Cronk

Hi Diego,

If your fiscal year starts on January 1st you can use =weeknumber(date@row)

If your fiscal year is not January 1st you'll need a different formula. If your fiscal years starts on October 1st the formula would be:

=ROUND(((Date@row - DATE(2021, 10, 1)) / 7) + 0.49, 0)

Substitute the 1st day of your fiscal year for DATE(2021, 10, 1)

Work for you?

Mark

Diego Casillas Zaragoza

Thank you so much! this worked like a charm and I really appreciate how you explained in detail since I have no idea how to work with formulas! thank you once again!

Mark Cronk

Hi Diego,

Glad you found a solution. Thank you for contributing to the Community.

Mark

Adriane Price

@Mark Cronk - this formula works perfect, curious can this formula be continued into the next fiscal year where it will update and start over at week 1?

Shreeraj

@Mark Cronk How would be able to modify the formula if there are 52 FY weeks instead of 56?