SOLVED

Changing cells formatting depending of interval of time/number of years

Copper Contributor

Hi,

I am recording the dates for 22 training certificates on Excell sheet (e.g Nov-22).

In my workplace there are 74 people working. 

Total of approx 1600 entries.

The certificates could expire after 1, 2 or 3 years.

 

At the moment I am changing manually the font colour if:

Green - if the certificate expires this year but it didn't expired yet (eg. if certificate awarded in Nov-22 expires after 2 years, now Apr-24 it would be green coloured).

 

Red - if the certificate expires this year and it is expired (eg. if certificate awarded in Apr-22 expires after 2 years, now Apr-24 it would be red coloured). Or if they are missing.

 

 

Could you please advise any formula, to get the font colour to change automatically depending on those variables.

Thanks

Luca

e95e2b15-6afd-4b0c-85c8-b04cb8148c2f.jpg

 

4 Replies
best response confirmed by HansVogelaar (MVP)
Solution

@LucaFloro 

Does the attached file work as intended?

Hi @OliverScheurich 

thank you for replying.

Yes it's the way it should be. I didn't include it for GDPR reasons, but on the left there is a column with the names of the employees.

Thanks

Luca

 

@OliverScheurich  It does not necessarily answer my question. 

My question was based on the use of formulas to position the cells in the form of numbering like in RANKING. 

For example the sheet below is a results of learners who participated in a an exam. The POSITION was done manually from highest learner to the lowest one based on TTL(Total marks) for 6 subjects the learner scored. 

So, I would like to help me how can I use the formula to do that instead of manually do it. 

 

image_2024-04-18_103539544.png

 

@OliverScheurich 

Hi Oliver,

I apologize. I didn't realize that you attached a file the first time you replied. I thought you were mentioning the picture I uploaded. I'm really sorry. 

 

Thank you It works perfectly!  

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

@LucaFloro 

Does the attached file work as intended?

View solution in original post