Using the WORKDAY formula issues

Copper Contributor

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. 

8 Replies
Delete “”)& after “n/a”, then add ) at the end of your formula.

@Twifoo 

 

Hi

 

Unfortunitly it is still returning the number of the date not the date itself

Please attach your sample file so I can see which issues are causing the formula to return the number rather than the date.

@emilysheffield , I guess you need to apply Date format to your resulting cell.

Hi @Sergei Baklan 

 

As explained above - the cell is formated to Date

Here u are@Twifoo 

I will examine your attached file tomorrow. I’m just replying now via mobile phone.

@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

image.png