Forum Discussion

Copper Contributor

Feb 07, 2023

Solved

Formula for if a date is more than X days in the past

Can someone help me with a formula for Excel? If Column A returns a set of dates, is there a function to show if the dates in Column A are more than 30, 60, 120 days in the past?

Formulas and Functions

mtarler
Feb 08, 2023
another option:
=XLOOKUP(TODAY()-A1, {30,60,120}, {">30",">60",">120"}, "<30", -1)
as for Mathetes try:
=IFS(TODAY()-A1>=120,"120",TODAY()-A1>=60,"60",TODAY()-A1>=30,"30",1,"<30")
one more option in case you have an old Excel version:
=VLOOKUP(TODAY()-A1,{-9999,"<30";30,">30";60,">60";120,">120"},2,1)

mathetes

Silver Contributor

Feb 08, 2023

O_edwardsKPPB-9

Yes.

=IFS(TODAY()-A1=>120,"120",TODAY()-A1=>60,"60",TODAY()-A1=>30,"30")

O_edwardsKPPB-9

Copper Contributor

Feb 08, 2023

mathetes That is giving me an error and does not work 😕

mtarler
Silver Contributor
Feb 08, 2023
another option:
=XLOOKUP(TODAY()-A1, {30,60,120}, {">30",">60",">120"}, "<30", -1)
as for Mathetes try:
=IFS(TODAY()-A1>=120,"120",TODAY()-A1>=60,"60",TODAY()-A1>=30,"30",1,"<30")
one more option in case you have an old Excel version:
=VLOOKUP(TODAY()-A1,{-9999,"<30";30,">30";60,">60";120,">120"},2,1)
- ALex1Rose
  Copper Contributor
  Mar 21, 2025
  This comes back with an error on my Microsoft 365, with Windows 11.
  Even ChatGPT can't give a formula for something as simple as a todays date being close to an expiry date.
  Either ChatGPT sucks, or 365 sucks.
  - SergeiBaklan
    Diamond Contributor
    Mar 22, 2025
    Formula from mtarler works practically on any Excel, includes 365. What exactly is wrong, from your screenshot it's not clear what you try to do. Task to have "todays date being close to an expiry date" also not clear, at least for me.