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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
17 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies