Results 1 to 3 of 3

Thread: Creating a Fiscal Week Lookup Table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular DPChristman's Avatar
    Join Date
    Sep 2012
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Creating a Fiscal Week Lookup Table

    I am trying to create a fiscal week lookup table for a spreadsheet I am working on. My boss needs TY/LY weekly comparison.
    I have tried to use the =weeknum(b1) command. However, our first fiscal week is 2/3/19-2/9/19, so the value returned using that formula is 6.
    I am able to get it to read 1 by changing the formula to =weeknum(b1)-5.
    However, three days into week 48 (Tues 12/31), the formula reports a value of '(4)' and decreases weekly to 0 on 1/26
    I know I can override the formulas with manual entries for the last few weeks, but I would like it to work properly
    Last edited by DPChristman; Jun 26th, 2019 at 11:32 AM.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    WA state
    Posts
    426
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating a Fiscal Week Lookup Table

    Try:
    =MOD(WEEKNUM([date cell]) - 5 - 1, 53) + 1

  3. #3
    Board Regular DPChristman's Avatar
    Join Date
    Sep 2012
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating a Fiscal Week Lookup Table

    Thanks. That works.
    However, it gives me 53 weeks and we have a 52 Week fiscal year
    So I just changed the 53 in the formula to 52, and it gives me exactly what I need

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •