expiration date code Closed

Question

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

venkat1926 · Answer

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

Expiration date code

1 response

Similar discussions

Subscribe To Our Newsletter!