SOLVED

How to use conditional formatting to highlight information after using =DATEDIF to calculate age?

%3CLINGO-SUB%20id%3D%22lingo-sub-3329370%22%20slang%3D%22en-US%22%3EHow%20to%20use%20conditional%20formatting%20to%20highlight%20information%20after%20using%20%3DDATEDIF%20to%20calculate%20age%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3329370%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%20this%20is%20my%20first%20time%20here.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20already%20using%26nbsp%3B%3CSTRONG%3E%3DDATEDIF(E3%2CTODAY()%2C%22Y%22)%20%26amp%3B%20%22Y%22%20%26amp%3B%20DATEDIF(E3%2CTODAY()%2C%22YM%22)%20%26amp%3B%20%22M%22%3C%2FSTRONG%3E%20to%20calculate%20their%20age%2C%20after%20that%2C%20I%20would%20like%20to%20use%26nbsp%3Bconditional%20formatting%20to%20highlight%20those%20results%20over%2059Y05M%2C%20what%20should%20I%20do%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3329370%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-3329930%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20conditional%20formatting%20to%20highlight%20information%20after%20using%20%3DDATEDIF%20to%20calculate%20age%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3329930%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1382553%22%20target%3D%22_blank%22%3E%40Jeffkcl%3C%2FA%3E%26nbsp%3BYou%20can%20use%20rule%20from%20the%20%22Highlight%20Cells%22%20group%2C%20Greater%20Than...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202022-05-06%20at%2007.33.47.png%22%20style%3D%22width%3A%20341px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369683i7D851168DDD9A887%2Fimage-dimensions%2F341x215%3Fv%3Dv2%22%20width%3D%22341%22%20height%3D%22215%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-05-06%20at%2007.33.47.png%22%20alt%3D%22Screenshot%202022-05-06%20at%2007.33.47.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAnd%20then%20just%20type%2059Y05M%20in%20the%20box%20to%20the%20right%20of%20%22greater%20than%22.%20It%20will%20create%20rule%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202022-05-06%20at%2007.34.20.png%22%20style%3D%22width%3A%20196px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369682i50D7CAD3A9FE69A9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-05-06%20at%2007.34.20.png%22%20alt%3D%22Screenshot%202022-05-06%20at%2007.34.20.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20result%20in%20formatting%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202022-05-06%20at%2007.38.45.png%22%20style%3D%22width%3A%20113px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369684i068CAE0A2FCEFC8C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-05-06%20at%2007.38.45.png%22%20alt%3D%22Screenshot%202022-05-06%20at%2007.38.45.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20above%20screenshots%20were%20taken%20on%20a%20Mac%2C%20but%20it's%20very%20similar%20on%20the%20PC%20version%20of%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3347979%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20conditional%20formatting%20to%20highlight%20information%20after%20using%20%3DDATEDIF%20to%20calculate%20age%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3347979%22%20slang%3D%22en-US%22%3EOh!%20It's%20work!%20But%20it%20didn't%20work%20before%2C%3CBR%20%2F%3EAnyway%2C%20thanks%20so%20much!%3C%2FLINGO-BODY%3E
New Contributor

Hi everyone, this is my first time here.

I already using =DATEDIF(E3,TODAY(),"Y") & "Y" & DATEDIF(E3,TODAY(),"YM") & "M" to calculate their age, after that, I would like to use conditional formatting to highlight those results over 59Y05M, what should I do?


Thanks!

2 Replies
best response confirmed by Jeffkcl (New Contributor)
Solution

@Jeffkcl You can use rule from the "Highlight Cells" group, Greater Than...

 

Screenshot 2022-05-06 at 07.33.47.png

And then just type 59Y05M in the box to the right of "greater than". It will create rule like this:

Screenshot 2022-05-06 at 07.34.20.png 

and result in formatting like this:

Screenshot 2022-05-06 at 07.38.45.png

The above screenshots were taken on a Mac, but it's very similar on the PC version of Excel.

Oh! It's work! But it didn't work before,
Anyway, thanks so much!