Link to home
Start Free TrialLog in
Avatar of Andreamary
Andreamary

asked on

Autopopulate field as Yes or No based on whether date in previous column is past a specific date

In my spreadsheet, Column A has the following consistent entry format (initials–date) as shown below:
AA–DD-MMM-YY
AA–DD-MMM-YY
Where 'AA' denotes the various users' two-letter initials (RB, SC, etc.)
(Please note that the dash between the initials and the date is an EN dash, not a regular dash)

I would like Column B to auto-populate based on the following conditions:
If date in Column A is on or before a specific date (ie., 21-Mar-17), then Column B = 'Yes'
If date in Column A is after the specific date (21-Mar-17) , then Column B = 'No'

The 'specific date' in the formula would be changed every two or three months, depending on our production cycle. For example, in two months time I would be changing the date from 21-Mar-17 to 27-May-17.

Would VBA be the better solution vs. a cell-based formula, given that the 'specific date' value would only need to be changed in once in the code?

I have attached a sample file to illustrate this question.

Thanks!
Andrea
EE_Sample_YesNo_Based_on_Date.xlsx
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Hi Andrea,

Do you need formula or VBA?

Formula would be:
=IF(TEXT(RIGHT(A2,9),"dd-mmm-yy")<TEXT(TODAY()-7,"dd-mmm-yy"),"Yes", "No")

Open in new window

Please find attached...If you need VBA, then let me know
EE_Sample_YesNo_Based_on_Date.xlsx
Avatar of Andreamary
Andreamary

ASKER

Thanks, Shum, for the quick response! Yes, actually, I'd prefer VBA if possible?

Cheers,
Andrea
Please try below, assuming your sheet name is Sheet1 & you need to fill result from C2 to the whole range:
Sub UpdateDeadLine()
Dim Ws As Worksheet
Dim LR As Long
Application.ScreenUpdating = False
Set Ws = Worksheets("Sheet1") ' Change the sheet name here
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row
Ws.Range("C2:C" & LR).FormulaR1C1 = "=IF(TEXT(RIGHT(RC1,9),""dd-mmm-yy"")<TEXT(TODAY()-7,""dd-mmm-yy""),""Yes"", ""No"")"
Ws.Range("C2:C" & LR).Value = Ws.Range("C2:C" & LR).Value
Ws.Range("C1").Select
Application.ScreenUpdating = True
End Sub

Open in new window

Thanks, Shum! I'll check this out. BTW, just confirming that the specific date (ie., 21-Mar-17) is a static value (it's a deadline date), so will that date stay fixed as 21-Mar-17 when I open the spreadsheet in the future?

Andrea
Andrea,

Please try to change date and check if formula works, if yes then go for VBA option.
I have used less 7 days as a deadline, that what you want?
Or is it every Tuesday?
It's actually a fixed date, and in this case it is 21-Mar-17.
Then you should have another column as Dead Line date then Comments. Let me work out for you.
Thanks, Shum...much appreciated. And sorry, I realized I should have mentioned that if the cell in column A is blank, than the cell in column B should be blank as well. Column B should only auto-populate when Column A is not blank...

Andrea
OK I have added a Deadline date in Column B, so your formula in Column C would be:
=IF(TEXT(RIGHT($A2,9),"dd-mmm-yy")<TEXT($B2,"dd-mmm-yy"),"Yes", "No")

Open in new window

And your VBA code would be:
Sub UpdateDeadLine()
Dim Ws As Worksheet
Dim LR As Long
Application.ScreenUpdating = False
Set Ws = Worksheets("Sheet1") ' Change the sheet name here
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row
Ws.Range("C2:C" & LR).FormulaR1C1 = "=IF(TEXT(RIGHT(RC1,9),""dd-mmm-yy"")<TEXT(RC2,""dd-mmm-yy""),""Yes"", ""No"")"
Ws.Range("C2:C" & LR).Value = Ws.Range("C2:C" & LR).Value
Ws.Range("C1").Select
Application.ScreenUpdating = True
End Sub

Open in new window

Now you don't need to worry about Static Dates.
EE_Sample_YesNo_Based_on_Date_v2.xlsm
BTW, is it possible to avoid having another column as a deadline date if we use VBA, and just include the deadline date in the code?
You mean you need an Input Box to enter Deadline date?
Thanks, but I don't think I need an Input Box. I'll have a look at what you've provided in more detail...
I would still recommend you have extra column as Deadline, because you may need different date for different row. Input Box will fill the whole range.

Please find attached modified with blanks.
EE_Sample_YesNo_Based_on_Date_v3.xlsm
The deadline date is a single date, and is fixed for several months, so I actually won't need a different date. I would prefer if the user did not have to click on an 'Update' button to auto-populate the column (trying to reduce their 'overhead'), but that it is automated so that as soon as the user populates Column A with their Initials + Date in the approved format, then 'Yes' or 'No' autopopulates accordingly. Is this possible?

Thanks!
Andrea
Hi,

Thanks again for your help. I've added a 'Deadline' column as suggested and have used the formula you provided in the sample spreadsheet uploaded with this comment, and the only tweak I need in the formula to make this work is to include the following condition:

If Column A is blank, then Column C should be blank

Thanks!
Andrea
EE_Sample_YesNo_Based_on_Date_Updat.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks so much, Shum, for your patience and quick responses...much appreciated! :-)

Cheers,
Andrea
You're Welcome Andrea! Glad I was able to help :)