View original (French)

Combine overdue formulas and conditional formatting.

Solved
Phenix1970 Posted messages 75 Status Member -  
Phenix1970 Posted messages 75 Status Member -
Hello,

I have a table containing the following columns:
Column A = file type
Column B = file reception
Column Y = expiration deadline

In column A, several file types exist (example: "PC" or "GN6") I would like a color to display on the entire row according to the file type that appears. (I can't manage to do it for an entire row but only on the cell)

Regarding the file type "PC"
Its maximum processing time is 4 months. I would like a formula that indicates that if the deadline is exceeded, the cell turns "red"; otherwise, it remains normal.

On the other hand, regarding the file type "GN6":
This file must be transmitted within 15 days before the event date which it concerns and must be studied immediately; it is urgent.

I tried to attach my file but I couldn't. Let me know how I can send you my file...

Do you think it is possible to combine formulas and conditional formatting?
Should I add a column (like for example a column indicating different deadlines as a reference?

I sincerely hope I am clear in my explanations.

Thank you very much for your help because I am not very good with calculations and I have been trying to apply these formulas and conditions for several months.

So once again THANK YOU

4 answers

  1.
    PapyLuc51 Posted messages 4567 Registration date   Status Member Last intervention   1 511
     
    Hello

    The two most commonly used sites to create a sending link:

    cjoint
    or
    mon-partage

    Please paste the link here

    Another note: OpenOffice or LibreOffice (for the number of MFC per cell)

    Best regards
    1
  2. ChrisPF13 Posted messages 234 Registration date   Status Member Last intervention   53
     
    Hello
    Here’s what I propose.

    The small bug is that the entire line turns red when the date is exceeded.
    Best regards
    1
    1. Phenix1970 Posted messages 75 Status Member
       
      Thank you for your help, I will try to apply myself tomorrow. Thank you very much.
      0
    2. Phenix1970 Posted messages 75 Status Member
       
      Good evening,

      As I just mentioned in the comments to PapyLuc51, I greatly thank you for all your help on the subject.

      However, I wanted to respond (if I may) to the little bug you pointed out regarding the entire line turning red when the date is overdue... In fact, for the overdue formula, it should be applied only to the cell unlike the other 3 conditions which should be applied to the selection of rows and cells chosen (for example, in my case, I selected my 3 columns and 300 rows).

      In any case, I thank you for everything. It is thanks to you and PapyLuc51 that I achieved the result.

      Have a good evening and thanks again.
      0
    3. ChrisPF13 Posted messages 234 Registration date   Status Member Last intervention   53
       
      Hello
      Thank you for the feedback regarding the red line.
      Wishing you all the best and have a nice day.
      0
    4. Phenix1970 Posted messages 75 Status Member
       
      Good evening,
      Well, that didn’t last long, here I am back. I have a problem with the overdue calculation formula which only takes into account the "PC" file type. However, I actually have other file types to consider, which also have a 4-month review period. These are the following files: PC that I had already mentioned to you, DAT, TRIM, PLE, INFO. I can’t seem to include them in the formula =IF($B3="PC",$D3+120,$D3+15) the formula is updated with my current columns, but it’s the one you suggested.
      Only the GN6 file type takes into account 15 days of review.

      Could you guide me?

      Also, could you explain to me the definition of $ in a formula?

      Thank you.
      0
  3. PapyLuc51 Posted messages 4567 Registration date   Status Member Last intervention   1 511
     
    Hello

    I just saw your file:

    For the line coloring, does it only concern PC and GN6 or others as well? If it only concerns these two types of files, follow the instructions given by ChrisPF13 but replace A with B in the formulas

    For GN6, you say "but receipt of the file 15 days before the event that will be proposed in the letter" - based on which column should we calculate the deadline for processing?

    For the processing deadline alert, the coloring only concerns column "Y" ; did I understand correctly?

    Best regards
    1
    1. Phenix1970 Posted messages 75 Status Member
       
      Good evening,

      As I said, I'm not at all friends with numbers, formulas, and everything that looks like them... but I enjoy challenging myself to understand and find solutions.

      Well, I've managed to do it and it's THANKS TO YOU, and I want to thank you for that.
      Even though I was very annoyed by the unexpected closure of LibreOffice, which made me constantly have to request document recovery... I don't understand why, but anyway.

      I really want to thank you once again immensely.
      I am very grateful for your help, your support, and also the time you dedicated to finding solutions to my inquiries.

      THANK YOU to PapyLuc51 and ChrisPF13

      (P.S.: it is not impossible that I will ask for new advice in this same category because I am already thinking about a little extra regarding formulas, but this time it will be with written mention such as "closed" or "in progress"... I will see)
      0
    2. PapyLuc51 Posted messages 4567 Registration date   Status Member Last intervention   1 511
       
      Hello and thank you for the response.

      If that's the case, please click on the gear icon next to the title to mark it as resolved.

      Best regards
      0
    3. Phenix1970 Posted messages 75 Status Member > PapyLuc51 Posted messages 4567 Registration date   Status Member Last intervention  
       
      Good evening,

      Finally, here I am back LOL. I have a problem with the overdue calculation formula proposed by ChrisPF13: =IF($B3="PC",$D3+120,$D3+15) which I updated with my current columns. It works very well, but it only takes into account the file type "PC" which has a 4-month processing time. Only the file type GN6 has a 15-day processing time.

      However, I do have other file types to consider that also have a 4-month instruction period. They are the following: PC that I already indicated, DAT, TRIM, PLE, INFO. I can't manage to include them in the formula. I'm really not good at this, and I've tried several modifications to the formula...

      Otherwise, I managed to assign a color for PC (blue), a color for GN6 (orange), and white for TRIM, PLE, INFO, and DAT.

      Could you guide me?

      Also, could you explain the definition of $ in a formula?

      Thank you.
      0
    4. PapyLuc51 Posted messages 4567 Registration date   Status Member Last intervention   1 511
       
      Good evening,

      So we need to reverse the IF condition since only GN6 is subject to the 15-day deadline.

      =IF($B3="GN6",$E3+15,$E3+120) and to leave the cell empty if B3 is empty

      =IF($B3<>"",IF($B3="GN6",$E3+15,$E3+120),"")

      Best regards.
      0
    5. Phenix1970 Posted messages 75 Status Member
       
      I will give it a try. I will keep you updated. Thank you for your help.
      Best regards
      0
  4. ChrisPF13 Posted messages 234 Registration date   Status Member Last intervention   53
     
    Hello
    In your first post, there were only 2 types of files (PC, GN6).
    Now, there are 6 types of files, it will be more complex.
    I will try to find a solution!
    Best regards
    1
    1. ChrisPF13 Posted messages 234 Registration date   Status Member Last intervention   53
       
      A little more complex, but I think it's not bad.
      I inserted a sheet "Format"
      https://www.cjoint.com/c/IBnnjxp85Kj
      Best regards
      0
      1. Phenix1970 Posted messages 75 Status Member > ChrisPF13 Posted messages 234 Registration date   Status Member Last intervention  
         
        I sincerely thank you for your help...
        0