Forum Discussion

MichaelMr's avatar
MichaelMr
Copper Contributor
Feb 11, 2022

Lists calculated column - Last 3 months without the current month (if not complete)

Hi,

 

I have a calculated column in lists that checks a column if the is the word " used" in it and then looks for all fields with "used" in the last 3 months.

 

=IF(AND(used="used",(DATEDIF([used date],(TODAY()),"D"))<90),1,0)

 

Is there a was to look for the last 3 complete months? For example:

Today is 11 of February, so February is not completed. Because of that I want to have the months November, December and January. When February is completed then, Dezember, January and February, ...

 

I tried =IF(AND(used="used",(DATEDIF([used date],(TODAY()-Day(TODAY()),"D"))<90),1,0)

to subtract the current day from today but this gives me the #NUM! error for a field where the date in the "used date" column is february 9th.

 

Lists
microsoft lists
SharePoint Online

1 Reply

  • MichaelMr's avatar
    MichaelMr
    Copper Contributor
    Feb 11, 2022
    I think I advanced a bit unsing the formula

    =IF(AND(usedtest="used",(DATEDIF([used date],((TODAY()-(DAY(TODAY())))),"D"))<=90),1,0)

    to subtract the amount of days in this months from today, but unfortunately it returns for the field that contains a date of the current month the #num! error.

    Any idea for this?