You are here:

Excel/If & workday formula



i have set it out like this but cell G142 is a drop down  box with these items (N/A, To Be Sent, Sent) but when i select N/A it works it picks up the date in cell F142 but If i unselect it it doesnt work anymore it wont look on the F140 cells date


i am doing something wrong but cannot seem to find it

can you please help thankyou Billy

ANSWER: Hi Billy,

Actually, if you select N/A in G142 your formula should yield the date in F140, not F142.  If you select To Be Sent of Sent your formula should select the date in F142.  If this is not what you want you should reverse these two arguments. However, the date given by the WORKDAY formula may not give different results for different values of G142 if the dates in F140 and F142 offset by 2 days both fall within holidays (as specified in L1:L12) or weekends.

When I tested your formula it worked properly for me.  I'm wondering if it is working properly for you, but you did not get the results you expected because the result did not change when you selected different options in F142 for the reason I explained above.

If I am misinterpreting your question please feel free to follow up.

Damon Ostrander

---------- FOLLOW-UP ----------

QUESTION: yeah works for me to but doesnt change when i select the drop down celli have attached a pdf that might help i would like it to change when selected

Hi again Billy,

I am unable to duplicate your problem.  When I deselect G142 or select a different option in the G142 dropdown, the value returned by the WEEKDAY function does change, and if the values in cells F140 or F142 change the WEEKDAY results change as they should.

I suggest you email me a copy of your workbook at so I can see for myself what the problem is and debug it.  

I am a little confused about one thing however.  You mention the problem occurring when you select the dropdown CELL, but also mention it occurring when you unselect the "N/A" value.  So I am not sure whether you are referring to selecting the CELL ITSELF or one of the VALUES IN THE CELL.  Either way the formula should yield the correct value based on the values in F140 and F142, but I do want to make sure I understand what you are doing.

Also, I have assumed that your "dropdown" in G142 is a data list validation dropdown (the simplest option for an in-cell dropdown).  If it is either a Forms Combobox or an ActiveX Combobox, this could help explain the problem, so please let me know if it is one of these.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Damon Ostrander


I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.


I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 All rights reserved.