vendredi 27 décembre 2019

Spreadsheet: Find the next Wednesday (Excel / Google Sheets)

I'm trying to display the date of the first upcoming Wednesday, to show the start time of a weekly event.

If it's Sunday 15/12/2019 then it would return Wednesday 18/12/2019.

If it's Monday 16/12/2019 then it would return Wednesday 18/12/2019.

If it's Wednesday 18/12/2019 then it would return Wednesday 25/12/2019.

I tried this function:

=TODAY()+(7-(WEEKDAY(TODAY(),3)-2))

(To test, I replaced TODAY() with A1 where A1 is a custom date: =A1+(7-(WEEKDAY(A1,3)-2)))

but on Mondays and Tuesdays it returns next week's Wednesday, rather than this week's.

So I solved it like this:

=IF((WEEKDAY(TODAY(),3)-2)<0, TODAY()-(WEEKDAY(TODAY(),3)-2), TODAY()+(7-(WEEKDAY(TODAY(),3)-2)))

(Test function: =IF((WEEKDAY(A1,3)-2)<0, A1-(WEEKDAY(A1,3)-2), A1+(7-(WEEKDAY(A1,3)-2))))

but this leaves me with a big chunky IF-function.

Since I am trying to keep the date as a variable to use in more complicated formulas, is there no way to more easily adjust the first function, without using complicated IFs?

Aucun commentaire:

Enregistrer un commentaire