• 547K Members
• 2,548 Online
• 652K Conversations

Highlighted
New Contributor

# Using the WORKDAY formula issues

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
Highlighted

# Re: Using the WORKDAY formula issues

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

# Re: Using the WORKDAY formula issues

Hi

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

Highlighted

# Re: Using the WORKDAY formula issues

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

# Re: Using the WORKDAY formula issues

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

Highlighted

# Re: Using the WORKDAY formula issues

As explained above - the cell is formated to Date

Highlighted

# Re: Using the WORKDAY formula issues

Here u are@Twifoo

Highlighted

# Re: Using the WORKDAY formula issues

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

# Re: Using the WORKDAY formula issues

@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

Related Conversations