Forum Discussion

billyrichrich83's avatar
billyrichrich83
Copper Contributor
Mar 08, 2023

Formula for financial year, 13 periods & 52/53 weeks

Hi, I was hoping someone could help. I inherited a spreadsheet at work that tracks document reviews and the times it has taken to do them, as part of this it tracks the week and period in the y...
excel
Formulas and Functions
billyrichrich83's avatar
billyrichrich83
Copper Contributor
Sep 13, 2023

mathetes - It's been a while, but I have been playing about with the formula you sent me through and trying to get it to work for my needs. I think I more or less got where I wanted to be, but now I'm receiving the Calculating (8 threads on the status bar, which takes ages to load or just completely crashes. any ideas why this will be happening? it also seems to use up a lot of memory and CPU usage whilst doing this. 

 

is this a processing issue or are the formulas a bit too much? 

 

I've attached an update for you to look at

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 13, 2023

billyrichrich83 

For me, your last used cell is BH1048566 and your formulas under the yellow headings reference entire columns.  Each of your calculations runs through a million or so cells.  Since you have a table, why not use structured references to limit the calculation and correct the used range.

 

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Sep 13, 2023

    Out of interest, an alternative formula for the first instance of a workday following any given date.

    = LAMBDA(date,[weekday],
    CEILING(date - weekday, 7) + weekday
  )

    As it stands, the formula treats 1 to be a Sunday and defaults to Saturday.  The following would use 1 as a Monday and default to Monday if the weekday is not specified

    = LAMBDA(date,[weekday],
    LET(
      wd, IF(ISOMITTED(weekday), 2, 1 + weekday),
      firstInstance, CEILING(date - wd, 7) + wd,
      firstInstance
    )
  )

     

    • billyrichrich83's avatar
      billyrichrich83
      Copper Contributor
      Oct 24, 2023
      Hi All,
      Sorry it's been a while been playing about with all the suggestion and finally got it working. Really appreciate all your help with this

Resources