Creating a Fiscal Week Lookup Table

DPChristman

Board Regular
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:

DPChristman

Board Regular
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

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top