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,

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

[TABLE="width: 637"]
<tbody>[TR]
[TD]May File[/TD]
[TD]June File[/TD]
[/TR]
[TR]
[TD]1st June 2020 on wards - Usable (>12)[/TD]
[TD]1st July 2020 on wards - Usable (>12)[/TD]
[/TR]
[TR]
[TD]1st December 2019 - 31st May 2020 - Usable (7-12)[/TD]
[TD]1st January 2020 - 30th June 2020 - Usable (7-12)[/TD]
[/TR]
[TR]
[TD]1st May 2019 - 30th November 2019 - Near Expiry[/TD]
[TD]1st June 2019 - 31st December 2019 - Near Expiry[/TD]
[/TR]
[TR]
[TD] Before 1st May 2019 - Expired[/TD]
[TD] Before 1st June 2019 - Expired
[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 637"]
<tbody>[TR]
[TD]May File[/TD]
[TD]June File[/TD]
[/TR]
[TR]
[TD]1st June 2018 on wards - Usable (>12)[/TD]
[TD]1st July 2018 on wards - Usable (>12)[/TD]
[/TR]
[TR]
[TD]1st December 2017 - 31st May 2018 - Usable (7-12)[/TD]
[TD]1st January 2018 - 30th June 2018 - Usable (7-12)[/TD]
[/TR]
[TR]
[TD]1st May 2017 - 30th November 2017 - Near Expiry[/TD]
[TD]1st June 2017 - 31st December 2017 - Near Expiry[/TD]
[/TR]
[TR]
[TD] Before 1st May 2017 - Expired[/TD]
[TD] Before 1st June 2017 - Expired[/TD]
[/TR]
</tbody>[/TABLE]

Let me know if it still not clear.
Click to expand...


I'm still confused.
Your question starts in June, all the examples were in June, I do not understand May.
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hi Dante,

Can you let me know what is confusing you now so that i can guide you better.

I did not understand by what you meant by all questions start in June.

In the above post i just wanted to make you understand as to how it needs to work every month. I need to run this code every month and hence every month the calculations of date should automatically push ahead by 1 month.

A simple example. Say my metric month is May 2019, so my expiry date for the category "Usable>12" will fall after May + 12 months which is June 2020 on wards. Likewise if my metric month is June 2019 which is the current month my "Usable>12" category will fall for all expiry date after June+12 months which is July 2020. In July it will +12 months again and will be August 2020 onwards.

I hope this works.
 
Upvote 0
mogss_04 said:
Hi Dante,

Can you let me know what is confusing you now so that i can guide you better.

I did not understand by what you meant by all questions start in June.

In the above post i just wanted to make you understand as to how it needs to work every month. I need to run this code every month and hence every month the calculations of date should automatically push ahead by 1 month.

A simple example. Say my metric month is May 2019, so my expiry date for the category "Usable>12" will fall after May + 12 months which is June 2020 on wards. Likewise if my metric month is June 2019 which is the current month my "Usable>12" category will fall for all expiry date after June+12 months which is July 2020. In July it will +12 months again and will be August 2020 onwards.

I hope this works.
Click to expand...

I am sorry, I do not understand.


I put what you tell me.

Then
Today June 6, 2019.


In the file you sent me.
To these dates of June 2020, what do I put them?

[TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_xl63, width: 64"]01.06.2020[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]30.06.2020[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]15.06.2020[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]19.06.2020[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]21.06.2020[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
They need to be populated as Usable 7-12 instead if "Usable>12".

Just help me understand the code that you have put ,it takes todays date as the base date for calculation of months right?
 
Upvote 0
mogss_04 said:
They need to be populated as Usable 7-12 instead if "Usable>12".

Just help me understand the code that you have put ,it takes todays date as the base date for calculation of months right?
Click to expand...

It works in this way

Code: 
    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"


'Yes, today's date is the base date to calculate the date ranges

'wDate it is the initial date. 
                                            year(today)  month(today) + 6 months  day
                                             2019            jun + 6 = 12 = dic              1
                                              2019              dic                                 1
    wDate = Format(DateSerial(Year(Date),   Month(Date) + 6,                1), "mm/dd/yyyy")

'wDat2 it's the final date

    fin = Day(DateSerial(Year(Date), Month(Date) + 12, 1) - 1)
                                       (2019       jun + 12 = jun    1)  -1
                                         (2020       jun                    1) -1      
                                         (2020        may                 31)
    fin = 31


    wDat2 = Format(DateSerial(Year(Date), Month(Date) + 12 - 1, fin), "mm/dd/yyyy")
                                             2019         jun + 12 - 1                 31
                                             2019         jun +  11                      31
                                             2020          may                           31   


                                                                                    2019/dic/1                    2020/may/31
    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 = "[COLOR=#0000ff]Usable (7-12)[/COLOR]"


Another example:

Code: 
                                             2019            jun + 12           1
                                             2020            jun                   1
    wDate = Format(DateSerial(Year(Date), Month(Date) + 12, 1), "mm/dd/yyyy")
                   
                                                                                   2020/jun/1
    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 = "[COLOR=#0000ff]Usable (>12)[/COLOR]"
 
Upvote 0
Hi Dante,

Let me know if my understanding is correct . As per above,

Any expiry dates falling between 1st Dec 2019 to 31st May 2020 will be classified as as "Usable (7-12)"

Any expiry dates on or beyond 1st June 2020 will be classified as "Usable >12 " . Is that correct what i have understood ?
 
Upvote 0
mogss_04 said:
Hi Dante,

Let me know if my understanding is correct . As per above,

Any expiry dates falling between 1st Dec 2019 to 31st May 2020 will be classified as as "Usable (7-12)"

Any expiry dates on or beyond 1st June 2020 will be classified as "Usable >12 " . Is that correct what i have understood ?
Click to expand...

It is right
 
Upvote 0
Hi Dante,



Okay . So then taking todays date as base range,

i want it to be 1st Jan 2020 to 30th June 2020 will be classified as as "Usable (7-12)

Any expiry dates on or beyond 1st July 2020 will be classified as "Usable >12 "

it should be +7 instead of +6 i guess in code. I dont know whether I am making sense. However not to confuse you anymore I want the calculations to appear as above.

Thank you Dante.
 
Upvote 0
Also for other two categories as below taking todays date as base range.

[TABLE="class: cms_table, width: 637"]
<tbody>[TR]
[TD]1.1st June 2019 - 31st December 2019 - Near Expiry
2. Before 1st June 2019 - Expired

Let me know if there are still any confusions[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table, width: 637"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
mogss_04 said:
Also for other two categories as below taking todays date as base range.

[TABLE="class: cms_table, width: 637"]
<tbody>[TR]
[TD]1.1st June 2019 - 31st December 2019 - Near Expiry
2. Before 1st June 2019 - Expired

Let me know if there are still any confusions[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table, width: 637"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

It is correct, for all cases take today's date
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Pulling Correct Number of Dates Based on Column Value
Replies
1
Views
41
dreid1011
dreid1011
M
  • Solved
Calculate headcount at the end of each month in 2019 based on hire and termination date
Replies
5
Views
5K
Peter_SSs
Peter_SSs
S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
2K
Alex Blakenburg
A
V
  • Question 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 Question
Macro Help - Move/Delete based on cell criteria
2
Replies
13
Views
2K
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,896
Messages
6,175,262
Members
452,627
Latest member
KitkatToby

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