Forum Discussion

denisemartincaldareri's avatar
denisemartincaldareri
Copper Contributor
Jun 20, 2020
Solved

Get Liquidation Period

Hi, everyone!

I would like to obtain the liquidation period of a date given a date range. If the date given falls between 2 dates, I need excel to give back the month. I can´t make it work! I get the error "wrong data type".

By the way, is there any chance to do this only considering the day and month? Thank you

The formula I use is this : =BUSCAR(H11;Y(H11<=Tabla4[Desde];H11>=Tabla4[Hasta]);Tabla4[Período de liquidación])

which is equal to:  VLOOKUP(H11;AND(H11<=Tabla4[Desde];H11>=Tabla4[Hasta]);Tabla4[Período de liquidación])

This is the example:

 
 

 

 

excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jun 20, 2020

    denisemartincaldareri Not sure I understand your date ranges. The first one says from 16-12-2020 to 15-01-2020. How would that work? The next is from 16-01-2020 to 15-02-2020. Thereafter, the start date goes back 11 months in time and the end date goes forward one month.

     

    Perhaps you just want to determine the termination month based on the termination date. If before the 15th then same month. If equal to or after the 15th then next month. If so, the formula could be:

     

    =IF(DAY(H11)<15,TEXT(EDATE(H11,0),"Mmmm"),TEXT(EDATE(H11,1),"Mmmm"))

     

     

     

    =SI(DIA(H11)<15;TEXTO(FECHA.MES(H11;0);"Mmmm");TEXTO(FECHA.MES(H11;1);"Mmmm"))

     

     

3 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jun 20, 2020

    denisemartincaldareri Not sure I understand your date ranges. The first one says from 16-12-2020 to 15-01-2020. How would that work? The next is from 16-01-2020 to 15-02-2020. Thereafter, the start date goes back 11 months in time and the end date goes forward one month.

     

    Perhaps you just want to determine the termination month based on the termination date. If before the 15th then same month. If equal to or after the 15th then next month. If so, the formula could be:

     

    =IF(DAY(H11)<15,TEXT(EDATE(H11,0),"Mmmm"),TEXT(EDATE(H11,1),"Mmmm"))

     

     

     

    =SI(DIA(H11)<15;TEXTO(FECHA.MES(H11;0);"Mmmm");TEXTO(FECHA.MES(H11;1);"Mmmm"))

     

     

    • denisemartincaldareri's avatar
      denisemartincaldareri
      Copper Contributor
      Jun 22, 2020

      Hi, Riny_van_Eekelen ! Thanks for your quick reply! Yes, I made a mistake in the example I sent, the Liquidation Period starts the 16th of the previous month and ends the 15th of the current month. Your formula works perfectly fine, I just added a "=" symbol to the  formula so as to consider the day number 15th on the previous month.

      Now it looks like this!

      =SI(DIA(H41)<=15;TEXTO(FECHA.MES(H41;0);"Mmmm");TEXTO(FECHA.MES(H41;1);"Mmmm"))

      Thank you!!!

       

Resources