May 23 2019 08:36 AM
Hi - Hoping some one can help.
I have written the below formula to deduct 20 working days from a meeting date if the meeting is in category B or to place an n/a in the cell if the meeting is in catergory A.
=IF(K3="A","n/a","")&IF(K3="B",WORKDAY(B3,-20, Guidelines!G16:G47),"")
K is the the cell where the meeting category is situated and B is the meeting date. I have added the rule for WORKDAY to exclude bank holidays. The formula works however the result that it creates is not a date (when that is what is needed) but the number of the date. This is despite me formatting the cell to show dates. I can not override the the formatting at all once this number is in the cell.
I am hoping some one can help.
May 23 2019 08:56 AM
May 24 2019 01:12 AM
May 24 2019 02:02 AM
May 24 2019 05:57 AM
@emilysheffield , I guess you need to apply Date format to your resulting cell.
May 24 2019 06:08 AM
May 24 2019 06:53 AM
May 24 2019 09:43 AM
@emilysheffield , you didn't correct your formula as @Twifoo suggested. Your current formula always returns empty string plus possible date for the option B, other words text & number. Excel interprets final result as text.
For the
=IF(K4="A","n/a",IF(K4="B",WORKDAY(B4,-20),""))
it returns number and with your format it will be shown as date