View original (French)

Last Tuesday or Thursday for the current month.

Solved
nonossov Posted messages 638 Status Member -  
jcbroadcast Posted messages 2 Status Member -
Hello,

Here is a formula that gives the last day of the month, but I need help finding the nearest Tuesday or Thursday closest to the end of the month. Thank you very much:

Example:
=DATE(YEAR(A1),MONTH(A1)+1,0)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),3)

Configuration: Windows / Firefox 52.0

3 answers

  1.
    PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   835
     
    Hello nonossov

    Try this formula

    For Tuesday

    =EOMONTH(A1,0)+CHOOSE(WEEKDAY(EOMONTH($A$1,0),2),-6,0,-1,-2,-3,-4,-5)

    For Thursday

    =EOMONTH(A1,0)+CHOOSE(WEEKDAY(EOMONTH($A$1,0),2),-6,0,-1,-2,-3,-4,-5)

    --
    It is by forging that one becomes a blacksmith. - It is at the foot of the wall that one sees the mason - one always learns from one's mistakes
    2
    1. jcbroadcast Posted messages 2 Status Member 1
       

      Great your formula except that it's the same for Tuesday and Thursday.
      I don't see which arguments to change to switch from one to the other.

      Thanks in advance

      Otherwise, I found this one: by putting the 1st day of the month in A1 (01/01/2023 for example)

      1. =EOMONTH(A1;0)-(7-3+WEEKDAY(EOMONTH(A1;0))) - Returns the last Tuesday
      2. =EOMONTH(A34;0)-(7-5+WEEKDAY(EOMONTH(A34;0))) - Returns the last Thursday
      0
      1. jcbroadcast Posted messages 2 Status Member 1 > jcbroadcast Posted messages 2 Status Member
         

        I had to refine it; here’s what it gives:

        =IF(EOMONTH(A1,0)-(7-C1+WEEKDAY(EOMONTH(A1,0)))+7>EOMONTH(A1,0),EOMONTH(A1,0)-(7-C1+WEEKDAY(EOMONTH(A1,0))),EOMONTH(A1,0)-(7-C1+WEEKDAY(EOMONTH(A1,0)))+7)

        I enter in C1 the day I want to retrieve: 1 for the last Sunday, 2 for the last Monday, ...

        1
  2. PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   835
     
    Hello

    When you receive name
    it's a function that is not present in your version of Excel!
    Check if you have all the functions of the formula

    --
    It is by forging that one becomes a blacksmith. - It is at the foot of the wall that one sees the mason - one always learns from one's mistakes.
    1
    1. nonossov Posted messages 638 Status Member
       
      I have a 2003 version, is there a possibility for it to work on 2003?
      0
    2. PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   835
       
      I don't know, what functions do you not have?
      0
    3. nonossov Posted messages 638 Status Member
       
      I have Excel 2003 and I tried to execute this formula =IF(WEEKDAY(EOMONTH(A1,0),2)>=4,EOMONTH($A1,0)+CHOOSE(WEEKDAY(EOMONTH($A1,0),2),0,0,0,0,-1,-2,-3),EOMONTH(A1,0)+CHOOSE(WEEKDAY(EOMONTH($A1,0),2),-4,0,-1,0,0,0,0)) but I did not succeed.
      0
      1. tontong Posted messages 2575 Registration date   Status Member Last intervention   1 064 > nonossov Posted messages 638 Status Member
         
        Hello,
        With 2003: Tools >> add-ins >> check Analysis ToolPak. Otherwise, EOMONTH() does not work.
        0
    4. nonossov Posted messages 638 Status Member
       
      Thank you very much :)
      0
  3. PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   835
     
    Hello nonossov

    For both at the same time

    =IF(WEEKDAY(EOMONTH(A1,0),2) >=4;EOMONTH($A1,0)+CHOOSE(WEEKDAY(EOMONTH($A1,0),2),0,0,0,0,-1,-2,-3);EOMONTH(A1,0)+CHOOSE(WEEKDAY(EOMONTH($A1,0),2),-4,0,-1,0,0,0,0))

    --
    It is by forging that one becomes a blacksmith. - It is at the foot of the wall that one sees the mason - one always learns from one's mistakes.
    0
    1. nonossov Posted messages 638 Status Member
       
      I don't know why the formula isn't working, I'm getting: #NAME?
      ??
      0