populate values in a cell based on date criteria

[mogss_04]

Hi All,

I required assistance on the below. Would be great if someone could help me out with it.

I have the below file.

https://www.dropbox.com/s/5wzide7bso...%203.xlsx?dl=0

I have to look at the below columns
I - Usage , column L - Batch creation Date , M - Batch Expiry Date and N - Inventory Flag

There is a coulmn name "Usage - I" which has the below 7 categories data . So 1st case we need to filter first by "Unrestriced use" and "Unrestricted-Use Mat" and then need to filter column "M - Batch Expiry Date". So if the expiry date falls 12 months after the the current month i.e 2020 June onwards then column N should be polpulated as"Usable (>12)", if expiry date falls between 7 - 12 months after expiry date
which is from December 2019 - May 2020 then column N should be polpulated as"Usable (7-12)" . Under "Unrestriced use" and "Unrestricted-Use Mat" anything before May 2019 that is current month should be populated as
"Expired" and if expiry date is from " May 2019 - November 2019" then column N should be poulated as "Near expiry".
Next we need to filter by "Blocked Stock" under column I and if it is Blocked stock then column N should be populated as Blocked irrespective of the expiry date column - M . Similary if column I is "Transit and Intransit" then column N should be populated as "Transit".Similary if column I is "Transit and Intransit" then column N should be populated as "Transit".Quality inspection - to be populated as "Quality inspection" under column N irrespective of expiry date
Valuated Goods Receipt Blocked Stock- to be populated as "Blocked" under column N irrespective of expiry date




1 Unrestricted Use
2. Transit
3.Blocked Stock
4.Unrestricted-Use Mat
5. Intransit
6.Quality inspection
7.Valuated Goods Receipt Blocked Stock


When " Batch Expiry Date" is blank or # we need to look at the "Batch Creation Date" column which is column "L" and the same rules apply. Exactly the same as expiry date rules except this goes 1 year back.


which is June 2018 onwards - Usable (>12)
Dec 2017 - May 2018 - Usable (7-12)
May 2017- Nov 2017 - Near Expiry
Before May 2017 -




If Manufacturing date and expiry date both are blanks or # present in both then N column should be poulated as Usable > 12.


Let me know how can we code the above and automate this procedure.

 

[mogss_04]

Hi Dante,

Even if we take todays date as calculation. All the dates beyond 10th June should be classified as "Usable >12 ".

However the below dates are still classified as "Usable (7-12)"

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]30.06.2020[/TD]
[/TR]
[TR]
[TD="class: xl63"]15.06.2020[/TD]
[/TR]
[TR]
[TD="class: xl63"]19.06.2020[/TD]
[/TR]
[TR]
[TD="class: xl63"]21.06.2020[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Hi Dante,

Even if we take todays date as calculation. All the dates beyond 10th June should be classified as "Usable >12 ".

However the below dates are still classified as "Usable (7-12)"

[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl63, width: 64"]30.06.2020[/TD]
[/TR]
[TR]
[TD="class: xl63"]15.06.2020[/TD]
[/TR]
[TR]
[TD="class: xl63"]19.06.2020[/TD]
[/TR]
[TR]
[TD="class: xl63"]21.06.2020[/TD]
[/TR]
</tbody>[/TABLE]

Remember that it takes June as a base, but it takes from June 1.

 

[mogss_04]

Exactly perfect. If it takes 1st June. Then these dates should be falling under category ""Usable >12 " as per the calcualtions. But instead it is falling under ""Usable (7-12)".Please check post #26 . You agreed on my understanding of rules that anything beyond 1st June 2020 comes under """Usable >12 "All the below 4 dates fall beyond 1st June 2020.

30.06.2020
15.06.2020
19.06.2020
21.06.2020

But its not happening this way. Can you please hence check on above.

 

[DanteAmor]

Exactly perfect. If it takes 1st June. Then these dates should be falling under category ""Usable >12 " as per the calcualtions. But instead it is falling under ""Usable (7-12)".Please check post #26 . You agreed on my understanding of rules that anything beyond 1st June 2020 comes under """Usable >12 "All the below 4 dates fall beyond 1st June 2020.

30.06.2020
15.06.2020
19.06.2020
21.06.2020

But its not happening this way. Can you please hence check on above.

This is the part of the code.

 wDate = Format(DateSerial(Year(Date), Month(Date) + [COLOR=#ff0000]6[/COLOR], 1), "mm/dd/yyyy")
    fin = Day(DateSerial(Year(Date), Month(Date) + 12, 1) - 1)
    wDat2 = Format(DateSerial(Year(Date), Month(Date) +[COLOR=#ff0000] 12 - 1[/COLOR], fin), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate, Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (7-12)"
    
    wDate = Format(DateSerial(Year(Date), Month(Date) + [COLOR=#ff0000]12[/COLOR], 1), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (>12)"

Change the month number, for example:

 wDate = Format(DateSerial(Year(Date), Month(Date) + [COLOR=#FF0000]6[/COLOR], 1), "mm/dd/yyyy")
    fin = Day(DateSerial(Year(Date), Month(Date) + 12, 1) - 1)
    wDat2 = Format(DateSerial(Year(Date), Month(Date) +[COLOR=#FF0000] 10[/COLOR], fin), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate, Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (7-12)"
    
    wDate = Format(DateSerial(Year(Date), Month(Date) + [COLOR=#FF0000]12[/COLOR], 1), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (>12)"

or

 wDate = Format(DateSerial(Year(Date), Month(Date) + [COLOR=#FF0000]6[/COLOR], 1), "mm/dd/yyyy")
    fin = Day(DateSerial(Year(Date), Month(Date) + 12, 1) - 1)
    wDat2 = Format(DateSerial(Year(Date), Month(Date) +[COLOR=#FF0000] 10[/COLOR], fin), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate, Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (7-12)"
    
    wDate = Format(DateSerial(Year(Date), Month(Date) + [COLOR=#FF0000]11[/COLOR], 1), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (>12)"