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