Forum Discussion

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...

Tracker WA.xlsx415 KB

excel

Formulas and Functions

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

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