How do I change the value of a cell based on the day

Question

I need to change the formula in a google sheets cell depending on whether it is a Monday or not.

If it is Monday, =min(GoogleFinance(A2, "LOW", Today()-3)) works fine as Today()-3 is a FRIDAY(last trading day).

However if it is any other day, i need to use, =min(GoogleFinance(A2, "LOW", Today()-1)) to show the PREVIOUS day's low.

is there an if statement that works for this? I think i get the structure,

=if (day == Monday), =min(GoogleFinance(A2, "LOW", Today()-3), =min(GoogleFinance(A2, "LOW", Today()-1))

but i'm not sure about the day == monday part.

Answer 1

Both excel and google-spreadsheet support the WORKDAY function. This allows you to specify a day that disregards weekends and optionally, a holiday list.

=GoogleFinance(A2, "LOW", workday(today(), -1))

I'm unclear on what MIN() is intended to accomplish. Perhaps something like,

=min(GoogleFinance(A2, "LOW", workday(today(), -1)), GoogleFinance(A2, "LOW", workday(today(), -2)))

For non-standard weekends, both platforms support WORKDAY.INTL which allows you to specify non-working days.

My personal recommendation is a fully expanded WORKDAY.INTL function that references a holiday list for your particular exchange.