Need to enter date

I've searched but can't find an answer to my issue.  I am trying to create a tool where managers can call Mandatory overtime by simply choosing MOT from a drop down menu.  When they do I want another cell to fill automatically with a date. 

For example,  our A shift employees work Sun.-Weds 10 hrs a day.  When MOT is called they will work on Thursday.  What I want is for the manager to choose MOT for A-Shift and another cell will automatically fill with the date for the next upcoming Thursday. 

I would appreciate any help.  If you need more info. please let me know.

 

Question Info


Last updated February 21, 2018 Views 48 Applies to:
Answer
Answer

If you choose a date prior to Thursday, then coming Thursday's date should come.

If you choose a date on Thursday itself, There are two scenarios possible -


1. This very Thursday's date should be coming. e.g if the date is chosen on 22-May-14, 22-May-14 will be displayed.

2. Next Thursday's date should be coming. e.g if the date is chosen on 22-May-14, 29-May-14 will be displayed.

Scenario 1 - Mike's formula will hold good which is

=IF(B1="MOT",TODAY()+7-WEEKDAY(TODAY()+2),"")

Scenarios 2 - Use below formula

=IF(B1="MOT",TODAY()+8-WEEKDAY(TODAY()+3),"")

Best regards
Vijay Verma

Timezone: UTC+05:30, PST+12:30
Availability Hours (UTC) : 05:00 AM - 05:00 PM

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Answer
Answer

Hi,

This assumes your MOT dropdown is in B1

=IF(B1="MOT",TODAY()+7-WEEKDAY(TODAY()+2),"")

If this response answers your question then please mark as answer.

Mike H

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.