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

Options

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?


Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • 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
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi Diego,

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Adriane Price
    Adriane Price ✭✭✭✭✭✭
    Options

    @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?

    Adriane

  • Shreeraj
    Options

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