Home

Using the WORKDAY formula issues

%3CLINGO-SUB%20id%3D%22lingo-sub-638194%22%20slang%3D%22en-US%22%3EUsing%20the%20WORKDAY%20formula%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-638194%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%20Hoping%20some%20one%20can%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20written%20the%20below%20formula%20to%20deduct%2020%20working%20days%20from%20a%20meeting%20date%20if%20the%20meeting%20is%20in%20category%20B%20or%20to%20place%20an%20n%2Fa%20in%20the%20cell%20if%20the%20meeting%20is%20in%20catergory%20A.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(K3%3D%22A%22%2C%22n%2Fa%22%2C%22%22)%26amp%3BIF(K3%3D%22B%22%2CWORKDAY(B3%2C-20%2C%20Guidelines!G16%3AG47)%2C%22%22)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EK%20is%20the%20the%20cell%20where%20the%20meeting%20category%20is%20situated%20and%20B%20is%20the%20meeting%20date.%20I%20have%20added%20the%20rule%20for%20WORKDAY%20to%20exclude%20bank%20holidays.%20The%20formula%20works%20however%20the%20result%20that%20it%20creates%20is%20not%20a%20date%20(when%20that%20is%20what%20is%20needed)%20but%20the%20number%20of%20the%20date.%20This%20is%20despite%20me%20formatting%20the%20cell%20to%20show%20dates.%20I%20can%20not%20override%20the%20the%20formatting%20at%20all%20once%20this%20number%20is%20in%20the%20cell.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20hoping%20some%20one%20can%20help.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-638194%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-638249%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20the%20WORKDAY%20formula%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-638249%22%20slang%3D%22en-US%22%3EDelete%20%E2%80%9C%E2%80%9D)%26amp%3B%20after%20%E2%80%9Cn%2Fa%E2%80%9D%2C%20then%20add%20)%20at%20the%20end%20of%20your%20formula.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643156%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20the%20WORKDAY%20formula%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643156%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunitly%20it%20is%20still%20returning%20the%20number%20of%20the%20date%20not%20the%20date%20itself%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643239%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20the%20WORKDAY%20formula%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643239%22%20slang%3D%22en-US%22%3EPlease%20attach%20your%20sample%20file%20so%20I%20can%20see%20which%20issues%20are%20causing%20the%20formula%20to%20return%20the%20number%20rather%20than%20the%20date.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643673%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20the%20WORKDAY%20formula%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643673%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347356%22%20target%3D%22_blank%22%3E%40emilysheffield%3C%2FA%3E%26nbsp%3B%2C%20I%20guess%20you%20need%20to%20apply%20Date%20format%20to%20your%20resulting%20cell.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643679%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20the%20WORKDAY%20formula%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643679%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20explained%20above%20-%20the%20cell%20is%20formated%20to%20Date%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644021%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20the%20WORKDAY%20formula%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644021%22%20slang%3D%22en-US%22%3E%3CP%3EHere%20u%20are%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644025%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20the%20WORKDAY%20formula%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644025%22%20slang%3D%22en-US%22%3EI%20will%20examine%20your%20attached%20file%20tomorrow.%20I%E2%80%99m%20just%20replying%20now%20via%20mobile%20phone.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644513%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20the%20WORKDAY%20formula%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644513%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347356%22%20target%3D%22_blank%22%3E%40emilysheffield%3C%2FA%3E%26nbsp%3B%2C%20you%20didn't%20correct%20your%20formula%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%20suggested.%20Your%20current%20formula%20always%20returns%20empty%20string%20plus%20possible%20date%20for%20the%20option%20B%2C%20other%20words%20text%20%26amp%3B%20number.%20Excel%20interprets%20final%20result%20as%20text.%3C%2FP%3E%0A%3CP%3EFor%20the%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3DIF(K4%3D%22A%22%2C%22n%2Fa%22%2CIF(K4%3D%22B%22%2CWORKDAY(B4%2C-20)%2C%22%22))%3C%2FPRE%3E%0A%3CP%3Eit%20returns%20number%20and%20with%20your%20format%20it%20will%20be%20shown%20as%20date%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20547px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F115948iDEBE20C11CBE0103%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
emilysheffield
New 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

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