Months remaining

Copper Contributor

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"))))

Screenshot 2024-07-07 123336.png

1 Reply

@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")))