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.