View original (French)

MFC date of the day between two dates in the table

Solved
Sandrine.ST.LPP Posted messages 4 Status Member -  
via55 Posted messages 14391 Registration date   Status Member Last intervention   -
Hello,

I would like to apply conditional formatting to the "Departure Date" (Column E) and "Return Date" (Column F) when today's date falls within those dates.
If possible, I would like the dates in the row to be displayed in green when today's date is between the departure date and the return date for individuals.
This is for conducting visits to residents in the municipality as part of the Operation Tranquility Absence, so we would know that this house needs to be checked this week.

I am using Excel 2016.

Thank you very much in advance.

PS: I do not know how to attach my file.

19 answers

  1.
    via55 Posted messages 14391 Registration date   Status Member Last intervention   2 759
     
    Hello

    Suppose the dates are in columns G and the following ones
    Select all these columns then Conditional Formatting - New Rule - Use a formula and enter the formula:
    =AND(G1>=$E1;G1<=$F1)
    making sure to respect the positions of the $ to freeze the start and end columns
    Choose the font color

    To attach your file, you need to upload it to a site like mon-partage.fr, create a link, copy it and come back to paste it here

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    1
    1. Sandrine.ST.LPP Posted messages 4 Status Member
       
      Je suis désolé, mais je ne peux pas accéder aux liens ou aux contenus externes.
      0
    2. Sandrine.ST.LPP Posted messages 4 Status Member
       
      I'm sorry, but I tried the manipulation, but it doesn't work.
      I added a column with today's date to match the example, actually if it's possible I'd like the formatting to apply to columns E and F with the date of "TODAY()"

      Anyway, thank you for your quick response.
      0
  2. via55 Posted messages 14391 Registration date   Status Member Last intervention   2 759
     
    Re

    Oh yes, I didn't understand today's date "hio, so like this:
    https://mon-partage.fr/f/iS2zltVV/

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    1
    1. Sandrine.ST.LPP Posted messages 4 Status Member
       
      Thank you very much, very much for your help.
      0
  3. via55 Posted messages 14391 Registration date   Status Member Last intervention   2 759
     
    Re

    Your file in return
    https://mon-partage.fr/f/vPU88aYp/

    It was all greyed out because you forgot the $ before the B
    For the holidays, I created a table with all the holiday days and used this table in a COUNTIF formula as you did for the public holidays

    For the application of the conditional formatting, they apply in order; for example, I set up the one for public holidays first, and August 15th appears in red

    --
    "Imagination is more important than knowledge." A. Einstein
    1
  4. valentinejade12 Posted messages 12 Status Member
     
    Hello,

    I believe I'm looking for exactly the same thing as you, but I've tried to download the file and I couldn't do it; it's no longer available.
    Could you please resend it to me so I can try to understand where my mistake is in my file?
    I've tried to reach out to you, but I don’t know how to contact you directly.
    I'm new to the site.
    Sorry for "squatting" your request.
    Have a nice day everyone!
    0
  6. via55 Posted messages 14391 Registration date   Status Member Last intervention   2 759
     
    Hello and welcome ☺

    I no longer have the file, but with the start date for example in column B and the end date in column C
    select the two columns then conditional formatting - new rule - Use a formula... and enter the formula:
    =AND($B1<>"",$B1<>"",$B1>=TODAY(),$C1<=TODAY())

    If you can't apply it to your file, upload it to mon-partage.fr, copy the link created and come back to paste it here, I will take a look

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  7. valentinejade12 Posted messages 12 Status Member
     
    Hi,

    Thank you for your response. I just tried to put it into practice, but it’s not working, maybe because the cells are on different sheets?
    I don't know, I'm getting desperate with this file.
    Thanks anyway for your reply!
    0
  8. via55 Posted messages 14391 Registration date   Status Member Last intervention   2 759
     
    Send me the file as requested with your attempt and I will see what is wrong

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  9. valentinejade12 Posted messages 12 Status Member
     
    https://mon-partage.fr/f/eGrHNQz4/
    0
    1. valentinejade12 Posted messages 12 Status Member
       
      Hello, I just managed to send you the file!
      Pfffff
      Thank you anyway for your attention!
      0
  10. via55 Posted messages 14391 Registration date   Status Member Last intervention   2 759
     
    I opened your file but I don't see any conditional formatting with TODAY
    Where do you want to put a conditional formatting and according to which criteria exactly?

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  11. valentinejade12 Posted messages 12 Status Member
     
    So my table might not be for today, it's mainly so that in my calendar the company vacation dates in the vacation dates tab appear with a gray fill, so that employees understand they don't have to note anything and that visually I can keep track.
    I would like to set the layout to 1 month per page and make sure that the conditional formatting does not override each other. For example, for Saturdays and Sundays, they need to be gray, and the other days white, but I don’t know what I did; everything is gray! Help!
    Thank you, you are more responsive than I am.
    0
  12. valentinejade12 Posted messages 12 Status Member
     
    How did you do that so quickly???
    Pfffff I understand, I thought there was another solution.
    Thank you very much anyway.
    Did you see that when I change the month, I get a macro error message?
    Could you tell me what I did wrong?
    In any case, really thank you very much for your help!
    0
  13. via55 Posted messages 14391 Registration date   Status Member Last intervention   2 759
     
    I saw, since there is no macro in the file I thought you sent me a saved copy without macro
    Take your original file back
    What is this macro supposed to do?

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  14. valentinejade12 Posted messages 12 Status Member
     
    No, I didn't do it on purpose, I tried to copy and paste the insertion of a dropdown list and it did this to me!
    I don't know why.
    0
  16. via55 Posted messages 14391 Registration date   Status Member Last intervention   2 759
     
    Yes, there seems to be an intrinsic macro, but I do not know how to access it!
    The easiest way in your workbook is to delete this drop-down control and then recreate one (Developer Tab - Insert), then with Design Mode enabled, right-click Format Control and in Input range, you put JoursDeSemaine!$D$1:$D$12, then in Linked Cell, you put $C$2

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  17. valentinejade12 Posted messages 12 Status Member
     
    I right-clicked on the dropdown area and went into macro and deleted what was there, and now I don't have an error message anymore. I hope I won't have any problems in the future for having done this action.
    THANK YOU for everything you've done; well, in your last message, you were speaking Chinese to me!!!
    I'm super limited; if I understand the procedure from a file, I get frustrated for a while, but somehow I manage! Just like with the public holidays, I found it a bit by chance!
    Thank you very much for all your help.
    See you soon.
    0
  18. via55 Posted messages 14391 Registration date   Status Member Last intervention   2 759
     
    Well

    See you soon

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  19. valentinejade12 Posted messages 12 Status Member
     
    I was wondering if I could take advantage of your skills to know if you think it's possible to create a macro or solution to reduce the lines when it comes to holidays, vacations, and Saturday and Sunday (non-working days)?
    Thank you!
    0
  20. via55 Posted messages 14391 Registration date   Status Member Last intervention   2 759
     
    You don't abuse ☺

    Would you like to hide the rows for non-working days?
    This can be done by a filter (provided you create an additional column that places an X next to the cells to be hidden based on the conditions of the conditional formatting formula)
    or by a macro triggered by a button or a keyboard shortcut
    Which solution do you prefer?

    --
    "Imagination is more important than knowledge." A. Einstein
    0