How would I calculate the previous Friday's date for a date entered Wed-Fri, or if Sat-Tues 2 Fridays before?

Trying to calculate dates to make payments to avoid late fees.


Subtracting 9 from entered date gives you a date in the previous week for Wed-Sun (using ISO week starting on Mon), and 2 weeks ago for Mon-Tue. Then subtracting weekday number and instead adding 5 gives Fri of the week we got on the previous step. Naturally, for Sat-Sun, this gives not the immediately preceding Fri on the same week, but the Fri on the previous week, this also satisfying the requirement.

Hmmmm, it says “CURRENT” is an unknown function.

CURRENT spreadsheet function. Check if you use localized function names, and if yout language makes this function have another name.

Which is the same as =A1-9-WEEKDAY(A1-9;2)+5

I’d rather avoid CURRENT() if it’s not to short-circuit repeated complicated calculations (where A1-9 is not complicated).

That worked perfectly. Thank you!