SOLVED

EDATE function - is this the right formula for my data

%3CLINGO-SUB%20id%3D%22lingo-sub-917500%22%20slang%3D%22en-US%22%3EEDATE%20function%20-%20is%20this%20the%20right%20formula%20for%20my%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-917500%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20attempting%20to%20devise%20a%20training%20tracker%20and%20have%20set%20up%20the%20EDATE%20function%20to%20show%20courses%20that%20are%20expiring%20at%20different%20intervals%20i.e.%201%2C%202%20and%203%20years.%20I%20have%20set%20up%20the%20conditional%20formatting%20to%20show%20when%20these%20courses%20are%2030%20days%20from%20expiring%20and%20everything%20appears%20to%20be%20working%20well.%20However%2C%20not%20all%20staff%20members%20require%20the%20same%20training%2C%20therefore%20i%20am%20deleting%20the%20EDATE%20formula%20within%20these%20cells%20as%20to%20not%20show%20the%20serial%20date%20which%20is%20then%20being%20highlighted%20as%20expired%20due%20to%20my%20condtional%20formatting%20rule.%20This%20is%20counterproductive%20as%20if%20needs%20change%20i%20am%20then%20adding%20the%20foruma%20back%20and%20as%20each%20staff%20is%20different%20i%20am%20not%20confident%20that%20this%20method%20will%20not%20fall%20foul%20to%20human%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20method%20for%20excel%20to%20ignore%20the%20corresponding%20cell%20EDATE%20is%20linked%20to%20when%20there%20is%20no%20data%20inputted%20to%20prevent%20this%20from%20occuring%3F%20If%20this%20is%20not%20possible%2C%20is%20there%20a%20different%20way%20i%20should%20be%20approaching%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrent%20formulas%20being%20used%3A%20%3DEDATE(K7%2C12)%20or%20%3DEDATE(L7%2C24)%20or%20%3DEDATE(M7%2C36)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EConditional%20Formatting%20being%20used%3A%20%3D%24P7%3CTODAY%3E%3C%2FTODAY%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReally%20would%20appreciate%20some%20direction%20on%20this%20so%20I%20can%20put%20the%20matrix%20in%20place.%20I%20have%20attached%20a%20screenshot%20of%20where%20i%20have%20got%20to.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-917500%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-919274%22%20slang%3D%22en-US%22%3ERe%3A%20EDATE%20function%20-%20is%20this%20the%20right%20formula%20for%20my%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-919274%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F427990%22%20target%3D%22_blank%22%3E%40tmales%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStep1)%20The%20formula%20that%20contains%20EDATE%20could%20read%20like%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3DIF(ISBLANK(K7)%2C%22%22%2CEDATE(K7%2C12))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20interpreted%20your%20question%20correctly%2C%20this%20will%20not%20calculate%20an%20end%20date%20for%20staff%20that%20do%20not%20require%20training.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20conditional%20formatting%20that%20you%20describe%20should%20work%20fine%20and%20it%20will%20not%20highlight%20cells%20that%20were%20left%20blank%20in%20step%201%20above.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi,

 

I am attempting to devise a training tracker and have set up the EDATE function to show courses that are expiring at different intervals i.e. 1, 2 and 3 years. I have set up the conditional formatting to show when these courses are 30 days from expiring and everything appears to be working well. However, not all staff members require the same training, therefore i am deleting the EDATE formula within these cells as to not show the serial date which is then being highlighted as expired due to my condtional formatting rule. This is counterproductive as if needs change i am then adding the foruma back and as each staff is different i am not confident that this method will not fall foul to human error.

 

Is there a method for excel to ignore the corresponding cell EDATE is linked to when there is no data inputted to prevent this from occuring? If this is not possible, is there a different way i should be approaching this?

 

Current formulas being used: =EDATE(K7,12) or =EDATE(L7,24) or =EDATE(M7,36)

 

Conditional Formatting being used: =$P7<TODAY()+30 ; i also have a rule in place which does not apply to cells that are blank.

 

Really would appreciate some direction on this so I can put the matrix in place. I have attached a screenshot of where i have got to.

 

Thanks

 

 

 

2 Replies
Highlighted
Solution

@tmales 

 

Step1) The formula that contains EDATE could read like:

 

 =IF(ISBLANK(K7),"",EDATE(K7,12))

 

If I interpreted your question correctly, this will not calculate an end date for staff that do not require training.

 

The conditional formatting that you describe should work fine and it will not highlight cells that were left blank in step 1 above.

 

Highlighted

@Riny_van_Eekelen 

 

Thanks for that - it has worked beautifully.