View original (French)

=DATE(ANNEE(AUJOURD'HUI()),MOIS(AUJOURD'HUI()),JOUR(AUJOURD'HUI())+55-DJOUR(AUJOURD'HUI()))+10-DJOUR(DATE(ANNEE(AUJOURD'HUI()),MOIS(AUJOURD'HUI()),1))+1

Solved
chti_ben -  
 Benoît -
Hello,
I'm trying to find the formula in Excel that allows me to calculate a correct due date for a 55-day end of decade condition, namely:
If invoice date in cell A6 = January 5, 09
Then due date = March 10, 09
because January 5, 09 + 55 = March 1, 09, so end of decade March 10, 09
For this specific case, the following formula is correct:
=IF(DAY(A6+55)<=10,DATE(YEAR(A6),MONTH(A6)+2,10),IF(DAY(A6+55)<=20,DATE(YEAR(A6),MONTH(A6)+2,20),IF(DAY(A6+55)<=30,DATE(YEAR(A6),MONTH(A6)+2,30),IF(DAY(A6+55)=31,DATE(YEAR(A6),MONTH(A6)+2,10)))))
(good digestion)
But for the following case, the calculation is incorrect:
If invoice date in cell A2 = January 1, 09
Then due date = February 28, 09
because January 1, 09 + 55 = February 25, 09, so end of decade February 28, 09.
But the same following formula gives a due date of March 30, 09.
Can someone help me?
Thanks in advance, friends.

Chti_Ben
Configuration: Windows XP Internet Explorer 6.0

15 answers

  1.
    michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello
    try (XL French...)
    =DATE(YEAR(A1);MONTH(A1);(DAY(A1)+55)+9-MOD(DAY(A1)+55;10))
    --
    Best regards, Michel
    1
  2. chti_ben Posted messages 15 Status Member
     
    Michel,
    Thank you very much.
    I am working on an Excel spreadsheet in English, what is the meaning of MOD in the formula (or the equivalent in English)?
    See you soon.
    Chti_ben
    0
  3. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    In English, MOD is said... MOD
    calculates the remainder of the division of one number by another
    for example
    MOD(28,10) returns 8
    MOD(30,10) returns 0
    etc.
    and don't forget to replace the semicolons with commas....
    --
    Best regards, Michel
    0
    1. irishrom
       
      Good evening Michel,

      That's quite a nice formula you've got there.
      Unfortunately, I'm afraid it may run into issues because of February 29.

      I tested the French version:
      the formula works fine from January 1st to February 28th.
      February 28, 2009 gives a due date of April 30, 2009.

      But for March 1, 2009, the formula returns a due date of April 28, 2009 (??)

      Then for March 5, 2009, the due date becomes May 8, 2009 (strange)...

      Unless I've made a mistake, Chti_Ben, can you please confirm on your end??

      Best regards,
      irishrom
      0
  4. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello,

    Indeed!...
    I'm looking, but it's true that February and 2012 are causing chaos: for now I don't get it...
    Irishrom, thank you for pointing out this error
    --
    Best regards, Michel
    0
  6. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Re,

    February and leap year = doliprane...

    to be verified!!!
    =IF((DAY(A1)+55>20)*(MONTH(DATE(YEAR(A1),MONTH(A1),DAY(A1)+55))=2),EOMONTH(A1+55,0),DATE(YEAR(A1),MONTH(A1),DAY(A1)+55+(10-MOD(DAY(A1+55),10))))

    eomonth ==> eomonth

    edit at 11:50

    modified formula!!...
    =IF((DAY((DATE(YEAR(A1),MONTH(A1),DAY(A1)+55)))>=20)*(MONTH(A1+55)=2),EOMONTH(A1+55,0),DATE(YEAR(A1),MONTH(A1),DAY(A1)+55+(10-MOD(DAY(A1+55),10))))

    explanation: If the day "+55" falls on the 20th or later and it is in February, we ship at the end of the month
    --
    Best regards, Michel
    0
  7. chti_ben Posted messages 15 Status Member
     
    Michel,
    It looks good except for a few cases that give a deadline on the 9th, namely:

    Invoice Date Invoice date + 55 Due date according to formula Actual due date
    03/02/2009 30/03/2009 09/04/2009 10/04/2009
    04/02/2009 31/03/2009 09/04/2009 10/04/2009
    05/04/2009 30/05/2009 09/06/2009 10/06/2009
    06/04/2009 31/05/2009 09/06/2009 10/06/2009
    05/06/2009 30/07/2009 09/08/2009 10/08/2009
    06/06/2009 31/07/2009 09/08/2009 10/08/2009
    06/07/2009 30/08/2009 09/09/2009 10/09/2009
    07/07/2009 31/08/2009 09/09/2009 10/09/2009
    05/09/2009 30/10/2009 09/11/2009 10/11/2009
    06/09/2009 31/10/2009 09/11/2009 10/11/2009
    05/11/2009 30/12/2009 09/01/2010 10/01/2010
    06/11/2009 31/12/2009 09/01/2010 10/01/2010
    06/12/2009 30/01/2010 09/02/2010 10/02/2010
    07/12/2009 31/01/2010 09/02/2010 10/02/2010

    We're almost there because all the other days of the year are correct.

    Thank you very much
    0
  8. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    I will look at all this after the demonstration: it will change my mind!
    See you later

    --
    Best regards, Michel
    0
  9. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Re,

    see this attachment
    https://www.cjoint.com/?dvsIKwwFPf

    Apparently, it's OK with an intermediate column "date+55", we could do without it, but it would complicate even more a formula that is already complicated!
    In fact, all the mess came from February months (ending on the 28th or 29th) and months with 31 days

    Edit at 7 PM:
    add what is in bold in the formula!
    =IF((DAY((DATE(YEAR(A2);MONTH(A2);DAY(A2)+55)))>=20)*(MONTH(A2+55)=2);EOMONTH(A2+55;0);B2+(10-MOD(DAY(B2);10))+IF(AND(DAY(B2)>=30;DAY(EOMONTH(B2;0))=31);1;0))
    hoping that this time....
    https://www.cjoint.com/?dvtgRxhOmk
    --
    Best regards, Michel
    0
    1. Benoît
       
      Hello Michel,

      Thank you for all these good explanations. I am unable to retrieve the attached table because the session has expired.
      Would it be possible for you to let me know the value of B2? I am having difficulty getting the correct value by the end of January.
      Thank you in advance.
      Benoît
      0
    2. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      Hello,

      you have the explanation for B2 in the post above, the date being in A2
      Apparently, it's okay with an intermediate column "date+55"
      so A2+55

      I no longer have the template (4 years old...)
      0
    3. Benoît
       
      THANK YOU Michel for this feedback
      well, after many hours, I'm still having difficulty...
      Thanks anyway...
      Have a nice day.
      Benoît
      0
  10. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hello,

    If I understand correctly, we are looking for a deadline 55 days out but shifted to the 10th of the month.

    If the dates start in A2, test this formula directly in a cell, for example C2, and increment downwards.

    =IF(DAY(A2+55)>10, DATE(YEAR(A2), MONTH(A2)+3, 10), DATE(YEAR(A2), MONTH(A2)+2, 10))

    A+
    0
  11. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    I just tried your formula, it is wrong in most cases....
    --
    0
    1. irishrom
       
      Hi Mike,
      it seems there is an error in your second formula too. sorry :)

      On the other hand, congratulations to Michel!!!
      the formula from 7 PM works wonders.
      the only issue: the j+55 that falls on the 10 leads to the 20.
      I would have liked a deadline on the 10 itself.
      but the formula is relentless: mathematically, the day of the 10 has already begun; therefore, the decade will end on the 20.

      I think it's difficult to do better despite the intermediate column.

      For your information, on a year-over-year basis, the DSO would be 60.55 days which falls below the LME2009.

      Once again, congratulations Michel... And thank you!

      Now we just need to find a way to apply this Excel formula in Oracle
      (E-Business version)... if there are any takers :)
      0
    2. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147 > irishrom
       
      Hi,

      Well, the main thing is that you have a solution to your problem

      Have a good day, everyone
      0
    3. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320 > irishrom
       
      Hi Irishrom, Chti_ben

      Thank you Irish** and glad that it finally works! I had asked the question about the 10th, 20th, end of the month and 30th of the decade, but I biased it by using the term "end of the decade."
      However, what is DSO and LME2009?
      Chti_ben, it would be good to mark the post as resolved if it suits you

      ** Irish? Did St. Patrick's Day go well?
      --
      Best regards, Michel
      0
    4. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320 > michel_m Posted messages 18903 Registration date   Status Contributor Last intervention  
       
      re,

      following the justified comment from Irishrom, attached is a proposal without an intermediate column
      https://www.cjoint.com/?dwkWg1jYQe
      --
      Best regards, Michel
      0
    5. irishrom > michel_m Posted messages 18903 Registration date   Status Contributor Last intervention  
       
      Hello Michel,

      the latest version of the formula, without an intermediate column, works wonders.
      A big thank you for removing this thorn from our side :)

      DSO = number of Days of Sales Outstanding
      it's a ratio used in customer accounting in large companies to measure collection performance.

      the formula: DSO = (total accounts receivable) / (3 months revenue * 91 days)

      I find this ratio very theoretical in itself. but its evolution over the months is interesting to analyze.
      In short, the closer the DSO is to the payment term granted in the contract, the better, meaning the client pays on time.

      The LME2009 is the new French law that reduces, among other things, payment terms to a maximum of 60 days.

      Best regards,
      Irishrom.

      PS: St. Patrick's Day apparently went well. I was at work that day...
      But this is the first year in a long time that it was "so nice" (usually it's windy, hailing, and 0 degrees).
      However, I do not recommend anyone to get scammed by travel agencies for a plane ticket to Dublin to attend this "beer festival".
      It's not as extraordinary as the media suggests... It's better to attend the lemon festival in Menton :) at least we can be sure the sun will be part of it.
      0
  12. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Re,

    Indeed, my formula calculated a due date 55 days at the end of the month shifted to the 10th

    For my part, I tested this formula over one year; you should test it on your side, starting from the first line and incrementing downwards!

    =IF(AND(DAY(A1+55)>=11;DAY(A1+55)<=24);DATE(YEAR(A1);MONTH(A1)+3;10);DATE(YEAR(A1);MONTH(A1)+2;10))

    Good night
    0
    1. OLOMIDE
       
      Thank you, I'm trying.
      0
    2. OLOMIDE
       
      Maybe I misplaced the information in my column, for example in A1, what did you put? And so on
      (just an example of the first row in the table (data)

      Thank you again.
      0
      1. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147 > OLOMIDE
         
        Hello Olomide,

        You should have opened your own discussion, this one is in a resolved status.
        This discussion was about the due date calculation 55 days end of month, if the due date fell before the 10th it was shifted to the 10th of the month and if it fell after the 10th it was shifted to the 10th of the following month.
        The starting date in this example is in A1 the formula in an empty cell first line and drag down if necessary

        =IF(AND(DAY(A1+55)>=11;DAY(A1+55)<=24);DATE(YEAR(A1);MONTH(A1)+3;10);DATE(YEAR(A1);MONTH(A1)+2;10))


        A+
        Mike-31

        A problem without a solution is a poorly posed problem (Einstein)
        0
  13. chti_ben Posted messages 15 Status Member
     
    Gentlemen,
    It works with the following formula:
    =IF((DAY(A2+55)>=20)*(MONTH(A2+55)=2),EOMONTH(A2+55,0),(A2+55)+(10-MOD(DAY(A2+55),10))+IF(AND(DAY(A2+55)>=30,DAY(EOMONTH(A2+55,0))=31),1,0))
    Thank you very much for your support.
    Chti_ben
    0
  14. OLOMIDE
     
    Hello Michel,

    I am interested in the formula for the shifted days, but I don't understand your formula well. Could you explain it better, as I am trying it and it doesn't work.
    0
  15. philtax
     
    Hello,

    while you’re talking about Excel

    I have a very silly question

    what is it exactly used for

    thank you
    -1
  16. philtax
     
    I'm having bad luck today

    Good evening everyone
    -1