View original (French)

Excel formulas for the first and second half of the month

Solved
DJAMALOS Posted messages 356 Registration date   Status Member Last intervention   -  
DJAMALOS Posted messages 356 Registration date   Status Member Last intervention   -

Hello, I would like to know if it's possible to help me see the 2 Excel 2019 formulas:

1st half and 2nd half of the month automatically.

Example:

Formula1: from 01/02/2024 to 15/02/2024

Formula2: from 16/02/2024 to the end of the month.

For billing.

Thank you.


3 answers

  1.
    Le Pingou Posted messages 12290 Registration date   Status Contributor Last intervention   1 481
     

    Hello,

    Indeed, it's also correct.

    Best regards.
    The Penguin

    1
  2. bigoudiii Posted messages 276 Registration date   Status Member Last intervention   91
     

    Hello,

    We can also do it, (slightly shorter):

    First half:

     =TEXT(EOMONTH(TODAY(),-1)+1,"dd/mm/yyyy")&" to "&TEXT(EOMONTH(TODAY(),-1)+15,"dd/mm/yyyy")

    Second half:

     =TEXT(EOMONTH(TODAY(),-1)+16,"dd/mm/yyyy")&" to "&TEXT(EOMONTH(TODAY(),0),"dd/mm/yyyy")
    1
    1. DJAMALOS Posted messages 356 Registration date   Status Member Last intervention   3
       

      Hello, yes that's it. Thanks to Pingou and to bigoudiii.

      0
  3. Le Pingou Posted messages 12290 Registration date   Status Contributor Last intervention   1 481
     

    Hello,

    One possibility:

    In A1 the date of the first of the month

    'Example; A1 =1.1.2024 'first half ="from "&TEXT(A1;"dd/mm/yyyy") &" to "&TEXT( A1+14;"dd/mm/yyyy") 'second half ="from "&TEXT(A1+15;"dd/mm/yyyy") &" to "&TEXT( EOMONTH(A2;0);"dd/mm/yyyy")

    Best regards.
    The Penguin

    0
    1. DJAMALOS Posted messages 356 Registration date   Status Member Last intervention   3
       

      Hello Pingou, thank you for your quick response. Is there another option without going through cell A1, for example by replacing it with date or today? I'm waiting for your help, thank you in advance.

      I think I've found it. Are these the correct formulas?

       1st fortnight =TEXT(DATE(YEAR(TODAY());MONTH(TODAY());1);"dd/mm/yyyy")&" to "&TEXT(DATE(YEAR(TODAY());MONTH(TODAY())+1;1)-15;"dd/mm/yyyy") 2nd fortnight =TEXT(DATE(YEAR(TODAY());MONTH(TODAY());1)+15;"dd/mm/yyyy")&" to "&TEXT(DATE(YEAR(TODAY());MONTH(TODAY())+1;1)-1;"dd/mm/yyyy")
      0