SOLVED
Home

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
tmales
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
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.

 

@Riny_van_Eekelen 

 

Thanks for that - it has worked beautifully.

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies