Forum Discussion
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...
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.
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
)
)
- 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
