Forum Discussion

MichaelSignall's avatar
MichaelSignall
Copper Contributor
Jul 07, 2024

Months remaining

Good morning,

I'm currently having an issue getting a formula to work.

currently column i contains expirations formatted as MMM-YYYY. I need column J to show either months remaining as a positive or months passed as a negative. In addition if column i shows N/A column j should just show a blank string. this is the code I have but I keep receiving either #Num! for months that haven't passed yet or the number 1494 for some odd reason.

this is the code I'm working with

=IF(OR(I3="",I3="N/A"),"",IF(TODAY()>=I3,DATEDIF(I3,TODAY(),"m")*(-1),IF(DATE(YEAR(12),MONTH(12),1)>TODAY(),DATEDIF(12,DATE(YEAR(TODAY()),MONTH(TODAY()),0),"m"),DATEDIF(12,EOMONTH(TODAY(),0),"m"))))

excel
Excel for web
training
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 07, 2024

    MichaelSignall 

    Shouldn't 12 in your formula be cell reference I3?

    Does this do what you want?

     

    =IF(OR(I3={"", "N/A"}), "", IF(TODAY()>=I3, -DATEDIF(EOMONTH(I3, -1)+1, TODAY(), "m"), DATEDIF(TODAY(), EOMONTH(I3, 0), "m")))

Resources