Find date from day

[jb]

I have a combo box with options as Sunday, Monday, Tuesday and so on upto Saturday.

Now I want to display date based on selection of a day.

For example, if I choose Monday and today is monday then it should display today's date.

But if today is not monday then it should display date of coming monday.

What will be the formula for this?

 

[keymaster]

@Jb...


Interesting question.


Assuming it is a form control combo box, upon selection it will return the position of value selected. So you will get 1 for Sunday, 2 for Monday... 7 for Saturday.


Then, use below formula to get to nearest weekday with that selection.

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


here x is your combo box selection value.

 

[Haseeb A]

Hello JB,


Another way: Assuming combo values are 1 for Sunday, 2 for Monday... 7 for Saturday.


=TODAY()-WEEKDAY(TODAY()-x)+7

 

[Kevin@Radstock]

Hi


May be, assuming your days in the combo box are numerical (Sunday-saturday)

=CEILING(TODAY()-ComboBox,7)+ComboBox

 

[keymaster]

@Haseeb & Kevin... Excellent (and super clever) solutions...