question about conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2513965%22%20slang%3D%22en-US%22%3Equestion%20about%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2513965%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%20I%20am%20trying%20to%20figure%20out%20how%20to%20create%20a%20function%20or%20use%20a%20tool%20in%20excel%20that%20allows%20me%20to%20create%20a%20result%20based%20on%20a%20date.%20I%20am%20creating%20eligibility%20and%20ineligibility%20lists%20for%20a%20job%2C%20and%20it's%20based%20on%20a%20set%20date.%20to%20be%20eligible%20it%20has%20to%20be%20before%20May%207%2C%20%26nbsp%3B2019.%20Ineligible%20is%20after%20May%207%2C%202019.%20I%20want%20the%20column%20that%20will%20says%20eligible%20or%20ineligible%20for%20each%20person's%20name%20to%20be%20set%20in%20the%20row%20after%20the%20date.%20Any%20idea%20how%20to%20do%20this%3F%20It%20doesn't%20seem%20to%20work%20when%20I%20try%20with%20%22a%20date%20occurring%20rule%20for%20formatting%20cells%22.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2513965%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2513986%22%20slang%3D%22en-US%22%3ERe%3A%20question%20about%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2513986%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1095037%22%20target%3D%22_blank%22%3E%40Katie17626%3C%2FA%3E%26nbsp%3BUse%20a%20formula%20in%20stead%20of%20conditional%20formatting.%20See%20picture%20below.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-07-04%20at%2008.05.05.png%22%20style%3D%22width%3A%20422px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F293355i3366A738B36E20F9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-07-04%20at%2008.05.05.png%22%20alt%3D%22Screenshot%202021-07-04%20at%2008.05.05.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2536074%22%20slang%3D%22en-US%22%3ERe%3A%20question%20about%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2536074%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bthank%20you%20for%20the%20help%20with%20this.%20I%E2%80%99m%20still%20confused%20with%20how%20you%20did%20the%20part%20with%20the%20B1%20name%20range%20cut%20off.%20Could%20you%20please%20explain%20further%3F%20I%20also%20don%E2%80%99t%20want%20to%20show%20the%20formula%20work%20on%20my%20spreadsheet%20if%20possible%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello! I am trying to figure out how to create a function or use a tool in excel that allows me to create a result based on a date. I am creating eligibility and ineligibility lists for a job, and it's based on a set date. to be eligible it has to be before May 7,  2019. Ineligible is after May 7, 2019. I want the column that will says eligible or ineligible for each person's name to be set in the row after the date. Any idea how to do this? It doesn't seem to work when I try with "a date occurring rule for formatting cells". 

4 Replies

@Katie17626 Use a formula in stead of conditional formatting. See picture below.

Screenshot 2021-07-04 at 08.05.05.png

@Riny_van_Eekelen thank you for the help with this. I’m still confused with how you did the part with the B1 name range cut off. Could you please explain further? I also don’t want to show the formula work on my spreadsheet if possible

@Katie17626 The formula I provided is a simple IF function. I named one cell "cutoff" and used that name as a reference. I could have used $B$1 also. If a date, in your case, perhaps in column A, is less than or equal to a cutoff date than "Eligible", otherwise "Ineligible".

 

Not sure what you mean when you say that you "don't want to show the formula work". So you want cells to display "Eligible" or "Ineligible" but not have a formula in these cells. Then you are talking VBA. Not my area of expertise. Sorry!

Katie,
You can do that by selecting Format/Format Cells/Protection/and check "Hidden". Before the formula does not show, you will have to Protect the sheet (format/protect sheet)