populate values in a cell based on date criteria

M

mogss_04

Board Regular
Joined
May 9, 2019
Messages
57
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.
 
Sort by date Sort by votes
mogss_04 said:
Hi Dante,

I tried all possible tests on sign changes which you highlighted above on below code

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

However the 6/1/2020 is still being populated as "Usable (7-12)" instead of "Usable (> 12)".
Click to expand...


The next period where it starts and where it ends.
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)"
Click to expand...

Today is June 4.
Then the period goes from December 4, 2019 to June 4, 2020.
OR
The period runs from December 1, 2019 to May 31, 2020.
In your rules you only put month-year, but to make the filter that must have day-month-year.


I need for each rule put start: day-month-year, end: day-month-year.
Maybe it's clear to you, but I do not know your process, so I had to put the day, in this case adding 6 months to the current day.


I await your comments.
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hi Dante,

I was performing this for May month. Like wise I would be performing every month.

1.So if the expiry date falls 12 months after the the current month i.e 1st June 2020 on wards any date then column N should be populated as"Usable (>12)",

2.If expiry date falls between 7 - 12 months after expiry date which is from 1st December 2019 -31st May 2020 then column N should be populated as"Usable (7-12)" .
3. Under "Unrestricted use" and "Unrestricted-Use Mat" anything before 1st May 2019 that is current month should
4. "Expired" and if expiry date is from " 1st May 2019 - 30th November 2019" then column N should be populated as "Near expiry".

I hope this makes a little bit clear. Let me know you require any more clarifications from my side.
 
Upvote 0
mogss_04 said:
Hi Dante,

I was performing this for May month. Like wise I would be performing every month.

1.So if the expiry date falls 12 months after the the current month i.e 1st June 2020 on wards any date then column N should be populated as"Usable (>12)",

2.If expiry date falls between 7 - 12 months after expiry date which is from 1st December 2019 -31st May 2020 then column N should be populated as"Usable (7-12)" .
3. Under "Unrestricted use" and "Unrestricted-Use Mat" anything before 1st May 2019 that is current month should
4. "Expired" and if expiry date is from " 1st May 2019 - 30th November 2019" then column N should be populated as "Near expiry".

I hope this makes a little bit clear. Let me know you require any more clarifications from my side.
Click to expand...

The tests were successful in May, but now we are June 4, and June 1 is between 7-12 months.
 
Upvote 0
Hi Dante,

Thanks for making it clear.I guess then same applies to then the dates which are appearing as "Expired" instead of "Near Expiry "

I would really be thankful if you could help solve the below as well since its on the same line but for other region.


This is for West file.West file link is below.


https://www.dropbox.com/s/437b14jhqd...West.xlsm?dl=0


My next region file involves the below. I will send you the file across for refrence.




path : "C:\Users\Priyanka Singh\Desktop\VBA code1"


I.)First we need to look at Column H - Expired Qty column. Unfilter "0" qty. Then look at "Batch Expiry Date" .






1)December 2019 onwards- Coulmn W should be populated as "Expired" and Column X to be populated as "Restricted"




2)May 2019 - November 2019 - Coulmn W should be populated as "Expired" and Column X to be populated as "Near Expiry"




3)anything before May 2019 - Coulmn W should be populated as "Expired" and Column X to be populated as "Expired"




Next look at column J- Near Expiry Qty. Unfilter "0" qty. For all these quantities Coulmn W should be populated as "Near Expiry" and Column X to be populated as "Near Expiry"




Next look at column N - NET Qty. Unfilter "0" qty.




1. June 2020 onwards - Usable (>12)




2. Dec 2019 - May 2020 - Usable (7-12)




And then same "When "expiry date" is blank or # we need to look at the MFG date 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 W - Usable and Column X column should be poulated as Usable > 12

Thank you so much for your assistance once again.
 
Upvote 0
mogss_04 said:
Hi Dante,

Thanks for making it clear.I guess then same applies to then the dates which are appearing as "Expired" instead of "Near Expiry "

.
Click to expand...

I am happy to help you with your requirements, but before you can comment me if the original requirement was covered.
If so, you could create a new thread for your next requirement.
 
Upvote 0
Hi Dante,

I understood the above and it works for me , but just wanted to know whether can it be considered as month start for the period starting from and month end for period ending for "Usable (7-12)" and "Near Expiry" in the month in which i run and not the date on which i run .

As in you said it is taking June 4 since we running on that date.Just wanted to know irrespective of the date we run in June it should be considered as June start.

Let me know if I am making sense and if that is possible.
 
Upvote 0
mogss_04 said:
Hi Dante,

I understood the above and it works for me , but just wanted to know whether can it be considered as month start for the period starting from and month end for period ending for "Usable (7-12)" and "Near Expiry" in the month in which i run and not the date on which i run .

As in you said it is taking June 4 since we running on that date.Just wanted to know irrespective of the date we run in June it should be considered as June start.

Let me know if I am making sense and if that is possible.
Click to expand...

Please, try this

Code: 
Sub populate_values()
    Dim sh As Worksheet, wDate As String, wDat2 As String, lr As Long, lr2 As Long, fin As Variant
    Set sh = Sheets("base")
    
    Application.ScreenUpdating = False
    
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    lr = sh.Range("B" & Rows.Count).End(xlUp).Row
    sh.Range("B1").AutoFilter Field:=8, _
        Criteria1:="=Unrestricted Use", Operator:=xlOr, Criteria2:="=Unrestricted-Use Mat"
    
    wDate = Format(DateSerial(Year(Date), Month(Date) + 6, 1), "mm/dd/yyyy")
    fin = Day(DateSerial(Year(Date), Month(Date) + 12, 1) - 1)
    wDat2 = Format(DateSerial(Year(Date), Month(Date) + 12 - 1, 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) + 12, 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)"
    
    wDate = Format(DateSerial(Year(Date), Month(Date), 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 = "Expired"
    
    wDate = Format(DateSerial(Year(Date), Month(Date), 1), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date), Month(Date) + 6, fin), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate, Operator:=xlAnd, _
                                         Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Near expiry"
    
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("B1").AutoFilter Field:=8, Criteria1:="=Blocked Stock"
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Blocked"


    sh.Range("B1").AutoFilter Field:=8, _
        Criteria1:="=Transit", Operator:=xlOr, Criteria2:="=CC In-Transit"
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Transit"


    sh.Range("B1").AutoFilter Field:=8, Criteria1:="=Quality Inspection"
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Quality Inspection"
    
    '****
    'When " Batch Expiry Date" is blank or #
    '****
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("B1").AutoFilter Field:=12, _
        Criteria1:="=#", Operator:=xlOr, Criteria2:="="
    
    wDate = Format(DateSerial(Year(Date) - 1, Month(Date), 1), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:=">=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (>12)"
    
    
    wDate = Format(DateSerial(Year(Date) - 1, Month(Date) - 6, 1), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date) - 1, Month(Date) - 1, fin), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:=">=" & wDate, Operator:=xlAnd, _
                                         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) - 2, Month(Date) - 1, 1), "mm/dd/yyyy")
    fin = Day(DateSerial(Year(Date) - 2, Month(Date) + 6, 1) - 1)
    wDat2 = Format(DateSerial(Year(Date) - 2, Month(Date) + 5, fin), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:=">=" & wDate, Operator:=xlAnd, _
                                         Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Near expiry"
    
    
    fin = Day(DateSerial(Year(Date) - 2, Month(Date) - 1, 1) - 1)
    wDate = Format(DateSerial(Year(Date) - 2, Month(Date) - 2, fin), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:="<=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Expired"
        
    
    '****
    'If Manufacturing date and expiry date both are blanks or #
    '****
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("B1").AutoFilter Field:=12, _
        Criteria1:="=#", Operator:=xlOr, Criteria2:="="
    
    sh.Range("B1").AutoFilter Field:=11, _
        Criteria1:="=#", Operator:=xlOr, Criteria2:="="
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable > 12"
    
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    MsgBox "Done"


End Sub

Try the first file that you sent me and tell me if you have problems with some date.
 
Upvote 0
Hi Dante,

I ran the test on the previous file assuming June is the month we running. Below are the certain mismatches

Batch Expiry date

1.)The below expiry dates falling in June 2020 are being populated as "Usable (>12)" instead of "Usable (7-12)"

01.06.2020
30.06.2020
15.06.2020
19.06.2020
21.06.2020

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>



Batch Creation Date (When Expiry date is blank or #)

1.) 05.06.2018 is being populated as "Usable (>12)" instead of "Usable (7-12)"

2.) 12.12.2017 is being populated as "Usable (7-12)" instead of "Near Expiry"

3.) 05.05.2017 is populated as "Near Expiry" instead of "Expired"
Also as per post #3 the below problem still exists

"[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 33333]When the usage is "Blocked Stock" Column N is being populated as "Expired" instead of "Blocked". These are for cases where Batch Expiry date is "#". I guess its getting populated on the basis of the code which is run later where Expiry date is "#". It should appear as Blocked irrespective of Expiry date when Usage is " Blocked Stock". "[/COLOR]
 
Upvote 0
mogss_04 said:
Hi Dante,

I ran the test on the previous file assuming June is the month we running. Below are the certain mismatches

Batch Expiry date

1.)The below expiry dates falling in June 2020 are being populated as "Usable (>12)" instead of "Usable (7-12)"
01.06.2020
30.06.2020
15.06.2020
19.06.2020
21.06.2020

<tbody>
</tbody>
Click to expand...

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)" .
Click to expand...

Those are your rules, from June onwards it must be "Usable (>12)"

Sorry, but now I'm very confused.
 
Upvote 0
Hi Dante,

Sorry if i am confusing you. To make it simple, if i am running the code in May, May should be my base month, if i am running in June then June should be my base month and so on for the other months following and the calculations as below. Giving you example for 2 months that is May and June, would be applicable similarly for all the other months as well.


1. For Batch Expiry date and filtering by "Unrestricted use" and "Unrestricted-Use Mat".

May File June File
1st June 2020 on wards - Usable (>12)1st July 2020 on wards - Usable (>12)
1st December 2019 - 31st May 2020 - Usable (7-12)1st January 2020 - 30th June 2020 - Usable (7-12)
1st May 2019 - 30th November 2019 - Near Expiry1st June 2019 - 31st December 2019 - Near Expiry
Before 1st May 2019 - Expired Before 1st June 2019 - Expired


<colgroup><col><col></colgroup><tbody>
</tbody>

2. For Batch Creation date when Batch Expiry Date is "#" and Blanks.

May File June File
1st June 2018 on wards - Usable (>12)1st July 2018 on wards - Usable (>12)
1st December 2017 - 31st May 2018 - Usable (7-12)1st January 2018 - 30th June 2018 - Usable (7-12)
1st May 2017 - 30th November 2017 - Near Expiry1st June 2017 - 31st December 2017 - Near Expiry
Before 1st May 2017 - Expired Before 1st June 2017 - Expired

<colgroup><col><col></colgroup><tbody>
</tbody>

Let me know if it still not clear.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Calculate headcount at the end of each month in 2019 based on hire and termination date
Replies
5
Views
4K
Peter_SSs
Peter_SSs
S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
1K
Alex Blakenburg
A
V
  • Question
VBA to copy column based on a header matching a cell value (date). The paste formulas into another column. Finally, hard code values after copying.
Replies
2
Views
4K
vekdas
V
cmschmitz24
  • Question
Macro Help - Move/Delete based on cell criteria
2
Replies
13
Views
1K
DanteAmor
DanteAmor
D
  • Question
Formula or alternative to populate a cell value based on the criteria of multiple other cells in a table.
Replies
2
Views
481
AlanY
A

Forum statistics

Threads
1,215,056
Messages
6,122,907
Members
449,096
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top