Through VBA I am trying to filter for today's date and all dates 30 days in the future as well as all dates 30 days in the past.
I am currently using the code below, but it only show dates 30 days in the future. I cannot get it to populate cells that are either 30 days in the future or 30 days in the past.
How can I create a formula to calculate a future date, when it is not a “specific day” in the future? I mean, I have today's date (Jul 15/08) and I need to calculate from that, when it will be the first Monday of November in 2009.
I have a table that includes 2007 data and 2008 data. When I was entering Jan 1 for 2008, it defaulted to 2007. I had the whole table completed with all the 2008 dates before I realized this. Is there a quick way to highlight all the 2008 data and change only the year?
I want to make a little chart for easy reference that tells me due dates for projects, based on estimated completion times.
I'm already using NETWORKDAYS to find the amount of working days between today and a due date, but I want to flip the formula around, and I'm having trouble getting the syntax right.
For example, column A reads:
0 1 2 3 5 7 10 15 20
Estimated completion times for various projects.
So I want column B to read the date that this would render. A1, value 0, would always produce today's date for B1. B2 would always read one business day into the future, B3 would read as 2 business days into the future, B4 as 3 business days into the future. Does that make sense, and B5 as 5 bd into future.
I want to take a date (ex. May 25, 08 is in cell A2) and calculate 30 days (A2+30)June 24, 08(A6) and what i am trying to due is calculate if it is past due.
I used the if command but it does not seem to be calcualting the date plus thirty, it is only taking the date that is listed. (ex. instead of calculating =IF("A2+30"
to set a conditional format and include a function code in it as well?
I want a spreadsheet to change the color/font of a cell/column based on a comparison of a "start date" cell and a "due date" cell. This is to automate the process of highlighting items that have gone past the due date in the second cell. I was trying to do this by looking at the "now()" function and comparing it to the due date.
I am trying to create a spread sheet in excel that will show a number of days past due counting from the due dates. see the attached xls file for details.
I have a dashboard which has dropdown boxes to pick out the data you want to see. From this data i then have graphs which are graphing 0s from the data making the graph look horrible.
I know I need to add NA() to the cells so that 0s dont graph, but all my data has formulas in and i don't know how to add the NA part to the existing formula.
my formula for each cell is =IFERROR(VLOOKUP($A9,'DB data'!$A$1:$HH$2003,COLUMN(FP1),FALSE),"-")
This looks up name such as "sign on time", then goes to another sheet and retrieves the data.
Where can i add the NA part. I've tried instead of the "-" at the end but doesn't work. and ive tried instead of FALSE and this stops the formula from working.
I have a simple table for tracking performance evaluations with 3 columns:
Employee Name (A), Date of Hire (B), Eval on File (C)
Evaluations are completed 6 months from Date of Hire (1 time) and on an annual basis and logged into column C.
I need to identify a list of evaluations that 30 days past due from the beginning of each month on a monthly basis.
I spent hours breaking the process down into steps (by adding extra columns to calculate 6 month due date, difference between current date and 6 month, due date for current year, and it still takes a long long long time.
There must be an elegant formula there that can tie it all together. I worked hours to research it and failed.
I put a scenario below to illustrate my table. As of 02/01/14, Anna Li's 6 month evaluation is past due, James and Johns' annual evals are past due as well.
Employee NameDOH Perf Eval Anna Li 6/8/2013No James Hawkins12/12/200612/12/2012 John Silver 1/8/20071/8/2013 Kim Woo 8/14/20078/14/2013 Sam Shell 6/6/20056/6/2013 Tanya Smith 7/1/2013 1/1/2014
I need a formula that can count items that are past due by up to 30 days, 60, or 90 days. I'd guess the total of all 3 values should = the total past due.
I have the total past due as =COUNTIF(A!I$1:I$278, "<=" & TODAY() )
I am creating a tracker to record, incoming, latest date of reply needed and actual date of reply. So I have column A= received, Column B = need to reply by, Column C = replied on. What I want in A is today’s date which is easy enough with ctrl+; is there a way of having it set that as soon as the cell is clicked on that date is activated and until then it remains empty?
What I have in B is A# + 20 this adds 20 days to the date entered in column A, again is there any way to have it set that this automatically activates when cells in column A are active and not until then, as if I enter that code in the cell and nothing is showing in cell A it gives a date of the 20/01/2008.
In C I have tried a couple of codes and they have not worked so what I would like is a code that when A and B are active it generates a countdown for days left to reply and if this countdown reaches zero then the whole line is flagged up in red as late. Lastly, is there a code that I can use that will automatically correspond with the A, B or C columns and match them with the same number cell in the other columns, sorry this isn’t so easy to explain but what I have to do at the moment is type in B is =A1+20 , A2+20, A3+20 and so on rather than just typing in A……+20 for column B to know it needs to associate itself with the same number cell in column A regardless what number it is.
Sometimes my due dates need to be on the 15th of the month, for which DLL and Donkey gave me: =DATE(YEAR(TODAY()), MONTH(TODAY()) + (DAY(TODAY()) > 15), 15)
This works great except sometimes the 15th is on a Sunday which means I need the result to be the 13th, and sometimes it's on a Saturday which means I need the result to be the 14th. FYI: I am a n00b..
What I need to do it create a traffic light icon, just the ones in Excel based on the dates within the field. If there is a date present which is past todays date then I would like it to display the green traffic light icon, if the date present is scheduled for a future date I would like the amber traffic light to show, and finally if there is no date present at all I would like it to display a red traffic light icon.
I need to determine a formula which will allow me to calculate a future date based upon a current date with varying time periods.
For example:
I have a bill which is paid on the 15th and last business day of each month. I would like to be able to see the next due date regardless of what day of the week it is.
I have a bill which is paid every other Tuesday. I would like to know the next due date without having to enter +14 for every due date in the future. In other words, it is preferable to be able to open the spreadsheet and automatically see the next due date, not use autofill to repeatedly add +14 to a previous date which would limit the # of future due dates that could be calculated.
I have a bill which is paid on the last business day of each month, not the last Friday of each month. I would need excel to return a value for the last day of the month which = Monday-Friday, regardless of what day of the week it may be as long as it isn't Saturday or Sunday(holiday exclusion would be nice but not required).
I have spent too many hours Googling and trial with mostly error in attempting this. I formatted the very first cell A1 for today's date =TODAY(); this is also dirrectly above the column with all the dates in it. I just want an entire row's contents to turn red if it is 15 days old or older. This =$A1<TODAY()-15 somewhat works, but it only turns the date cell red, not the whole row. It also makes blank cells turns red, which I would like to remain blank (another formula is needed I imagine).
I found this nice little bit of code for a date range search in column A but it will not work. Apparently i have not set a variable or something.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim startDate As String Dim stopDate As String Dim startRow As Integer Dim stopRow As Integer startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)") If startDate = "" Then End stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)") If stopDate = "" Then End startDate = Format(startDate, "dd/mm/yyyy") stopDate = Format(stopDate, "dd/mm/yyyy") startRow = Worksheets("sheet1").Columns("A").Find(startDate, _ LookIn:=xlValues, lookat:=xlWhole).Row stopRow = Worksheets("sheet1").Columns("A").Find(stopDate, _ LookIn:=xlValues, lookat:=xlWhole).Row Worksheets("Sheet1").Range("A" & startRow & ":A" & stopRow).Select
I'm actually trying to write a Macro that applies autofilters and the information just doesn't show! The autofilter is properly applied and everything, but the only visible row is the header. The code is the following.
= LOOKUP(BH23,B1:AF1) this is looking for a date (a returned value from another set of equations) in a row of other dates. I want a macro to use this lookup, find the date and then select it as an auto filter, with criteria? here is the macro and the part where it says auto filter is the part where I want it to do the above lookup function
Sub Brooke() ActiveWindow.ScrollWorkbookTabs Sheets:=-4 Sheets("Feb 03").Select Selection.AutoFilter Field:=28, Criteria1:="BH" Range("AD7:AD56").Select Selection.Copy Sheets("Brooke Hotel Running Order").Select ActiveSheet.Paste ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("Feb 03").Select Range("AD62:AD68").Select Application.CutCopyMode = False Selection.Copy Sheets("Brooke Hotel Running Order").Select Range("B32").Select ActiveSheet.Paste ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 Range("F23").Select End Sub
The 'Details' sheet has, among other data, the 'year' that the workbook refers to. From this, the beginning and end dates of each quarter appear in Cells F2:F10 in the 'Details' Sheet in the format dd/mm/yyyy.
In the 'Management' Sheet I have four buttons - Q1, Q2, Q3 and Q4. I want to attach code to apply a custom autofilter to each button so that clicking on one of the buttons will show only data from that quarter.
Using the macro recorder the following code is generated when applying a custom AutoFilter for Quarter One:
I have this script it does exactly as i want it to do, filter a table of date using two selected date in D3 and D4. There is only one problem: when i choose the dates and run the script it changes them around it around. e.g cell D3 = 01/11/2006 cell D4 = 01/12/2006 so it should just show all of november (11) but D3 will filter using 11/01/2006 and D4 will use 12/01/2006
I recorded a macro to custom AutoFilter 2 columns in a worksheet. The information will be filtered between 2 dates; i.e. >= cell B2 and <= cell C2.
Both cells are formated to dd/mm/yy so i entered 1/12/06 (for 1st Dec) in cell B2 and 31/12/06 (for 31st Dec) in cell C2.
When i enter these 2 dates, click the button to run the macro and check the custom filter in the "Planning" worksheet it appears that the dates change to 12/01/06 and 21/12/06 and no records appear - although i know at least 65 rows should show.
If i then enter the dates in B2 and C2 as mm/dd/yy instead (but leave the date format of the cell as dd/mm/yy) the filter works perfectly; i.e. enter 12/01/06 and 12/31/06. However I plan to issue this spreadsheet out to other staff and as every other date in the spreadsheet is in the uk format dd/mm/yy i dont want to confuse them by insisting they use the mm/dd/yy format for this one function.
Has anyone ever encountered this before? and if so is there some way i can fix it?
Heres the code for the filter:
Sub Monthly_Stats()
Sheets("Planning").Select Selection.AutoFilter Field:=5, Criteria1:=">=" & Sheets("Filtered Statistics").Range("B2").Value, Operator:=xlAnd _ , Criteria2:="<=" & Sheets("Filtered Statistics").Range("C2").Value, Operator:=xlAnd Selection.AutoFilter Field:=82, Criteria1:="<>" End Sub
I am encounting problems with an AutoFilter which is run in a macro after the user has selected dates from 2 Date Pickers (DTPicker) on a user form. The filter is applied to 1 column in a spreadsheet containing dates in the formay "dd/mm/yy" and is supposed to filter dates =2 and between a range of dates as selected. The resultant filter is blank, if the macro is debugged at this point the date format for the 1st criteria is found to have swopped the "dd" & "mm" around but the 2nd criteria is fine. eg. user selected 01/07/07 and 31/07/07 the resultant filter applied is 07/01/07 and 31/07/07. I have re-formatted dates to Long as per the article on this site @ http://www.ozgrid.com/VBA/autofilter-vba-dates.htm but still get the same result ???
Dim fDate, tDate As Date Dim lfDate, ltDate As Long fDate = frmReportMenu.dtpFromDate.Value tDate = frmReportMenu.dtpToDate.Value fDate = DateSerial(Year(fDate), Month(fDate), Day(fDate)) tDate = DateSerial(Year(tDate), Month(tDate), Day(tDate)) lfDate = fDate ltDate = tDate
The problem is that when I add dates via macros (like from a userform to a selected cell) to a cell in autofiltered column, the autofilter doesn't recognize these values as "proper" dates. If I activate the formula bar and press Enter, then the autofilter identifies the date properly.
This is how the autofilter popup looks like after I have added a date via my macro: [URL] ....
The new date doesn't get categorized by year and month, instead it just shows as text on a separate line (circled in red) (huhtikuu = April)
I have earlier used Formula:
[Code] ....
How I could get the autofilter working right with the inputed values? I have tried adding the date via .Value and .Formula but neither one works.
I have a column of dates: 1 day per row: 01/01/10, 02/01/10, 03/01/10, etc in sequence, without gaps (European date format). The list can begin with a different start date depending on initial user input and is not always a Monday. I want the user to be able to autofilter so that it only shows dates that fall on a Monday.
Private Sub Worksheet_Activate() Dim dDate As Date Dim strDate As String 'disable autofilter if already enabled With ActiveSheet .AutoFilterMode = False End With 'autofilter column a only, from a12, to filter Mondays If IsDate( Range("a12")) Then dDate = Range("a12") strDate = Weekday(dDate) Range("a12").AutoFilter Range("a12").AutoFilter field:=1, Criteria1:=strDate End If End Sub
