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
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
ASKER
Thanks, Shum, for the quick response! Yes, actually, I'd prefer VBA if possible?
Cheers,
Andrea
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
Please find attached...
EE_Sample_YesNo_Based_on_Date_v1.xlsm
EE_Sample_YesNo_Based_on_Date_v1.xlsm
ASKER
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
Andrea,
Please try to change date and check if formula works, if yes then go for VBA option.
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?
ASKER
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.
ASKER
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
Andrea
OK I have added a Deadline date in Column B, so your formula in Column C would be:
EE_Sample_YesNo_Based_on_Date_v2.xlsm
=IF(TEXT(RIGHT($A2,9),"dd-mmm-yy")<TEXT($B2,"dd-mmm-yy"),"Yes", "No")
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
Now you don't need to worry about Static Dates.EE_Sample_YesNo_Based_on_Date_v2.xlsm
ASKER
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?
ASKER
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
Please find attached modified with blanks.
EE_Sample_YesNo_Based_on_Date_v3.xlsm
ASKER
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
Thanks!
Andrea
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much, Shum, for your patience and quick responses...much appreciated! :-)
Cheers,
Andrea
Cheers,
Andrea
You're Welcome Andrea! Glad I was able to help :)
Do you need formula or VBA?
Formula would be:
Open in new window
Please find attached...If you need VBA, then let me knowEE_Sample_YesNo_Based_on_Date.xlsx