Expiration date code

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I need help in creating a formula. I have a borne on date for a product that will expire in 24 months and want to automate the expiration date. say cell A1= Date of 03/08 and B2=03/10 and I want to show that if it has 3 months or less left on the expiration date to highlight Red. If the date has 3-6 months to expire to highlight cell in yellow. can someone suggest the proper formula?

Thanks

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
you have mentioned in A1 and B2 perhaps this is typo. it should be A2 and B2
you have entered date as march 08 and march 10 perhaps dates are not important
you also perhaps mean that month of expiration date minus month of today is less than or equal to 3 months then that expiration date should be marked red. for e.g. march 10 and now Jan 10 less than 3 months so red.
If you are familiar with macro I am giving a macro here.
If you dates are important this macro will give you wrong coloring

Sub test()
Dim r As Range, c As Range, j As Object
Worksheets("sheet1").Activate
ActiveSheet.Cells.Interior.ColorIndex = xlNone
Set r = Range(Range("B2"), Range("B2").End(xlDown))
For Each c In r
If Month(c) - Month(Date) <= 3 Then
c.Interior.ColorIndex = 3
GoTo line1
End If
If Month(c) - Month(Date) > 3 And Month(c) - Month(Date) <= 6 Then c.Interior.ColorIndex = 6
line1:
Next c
End Sub
0