Return of previous day based on selection

[tomleitch]

Hi,

I'm wondering if anyone can help.

I have a userform which has days of the week (mon through to sunday) as a selection and whatever day is selected is entered as a text into a cell...


what I want to do from this is work out the actual date of the previous day that has been selected:

i.e. if the user chooses tuesday then I want to return a date of what the previous tuesday was.


Thanks
Tom

 

[AlphaFrog]

Cell A1 is the weekday value

=TODAY()-WEEKDAY(TODAY()-MATCH(A1,{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},0))

 

[tomleitch]

Hi AlphaFrog, and thanks for your help.

That's doing pretty much exactly what I want - however there is one thing I wanted it to do. (maybe should have explained better)

If today is Sunday 15th April and the day Sunday is selected - then I want it to return todays's date, rather than the previous week's sunday.

Is this possible?

Thanks
Tom

 

[AlphaFrog]

Hi AlphaFrog, and thanks for your help.

That's doing pretty much exactly what I want - however there is one thing I wanted it to do. (maybe should have explained better)

If today is Sunday 15th April and the day Sunday is selected - then I want it to return todays's date, rather than the previous week's sunday.

Is this possible?

Thanks
Tom

=TODAY()+1-WEEKDAY(TODAY()+1-MATCH(A1,{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},0))

 

[tomleitch]

That's done the business - many thanks