Avg Based On Date Range
Jul 22, 2007
I am diabetic and track my blood sugar on a spread sheet I created. Each month is a page named for the month and year. ( ie: may07, June07 etc ) The last page is my averages page. I monitor averages from the begining as well as several 'last 3 month' statistics. At this time I have to go in each month and change the months on the '3 month averages' formulas then add a month to the 'over all' formula. I want to use a formula that will simply look at the last 3 months without having to manually change the months as well as the 'overall' formula automatically calculating "all" months without adding a new month every month.
And last but not least, I have some sub-total boxes giving a div by zero error with blank cells. What am I doing wrong? Not all boxes do this even though the code is copied and pasted so they all 'appear' identical. Rather than try to paste my code here you can see my spreadsheet at [url]
View 10 Replies
ADVERTISEMENT
Mar 5, 2014
based on user date ranges entered on sheet1, I'm trying to write code that will write each month of the date range on other sheets across the 2nd row. at this point I'm getting "object required" error at "Set DateStart = Cells(2, 6)"
I also want the date format to be mmm-yy (Mar 14) on the sheets even if sheet1 has a different format. I tried using sourcerange instead of DateStart, but that didnt work either.
Code:
Dim projStartDate As Date
Dim projEndDate As Date
Dim DateStart As Date[code]....
View 1 Replies
View Related
Jul 1, 2008
I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.
To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in
D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet
View 2 Replies
View Related
Mar 28, 2014
I'm trying to combine the hours that a employee worked on a single date, with one of multiple time periods that exist for that employee.
I have two sets of data.
Set 1 (hours)
Employee number, date, hours
12345, 1-2-2014, 6
12345, 1-3-2014, 8
12345, 1-10-2014, 8
Set 2 (periods)
Employee number, start date, end date
12345, 1-1-2014, 4-1-2014
12345, 6-1-2014, 1-2-2014
What I'd like to do is to add the start and end date of Set 2 to Set 1 for every row in Set 1
In above example the result should be like this.
12345, 1-2-2014, 6, 1-1-2014, 4-1-2014
12345, 1-2-2014, 8, 1-1-2014, 4-1-2014
12345, 1-10-2014, 8, 6-1-2014, 1-2-2014
View 13 Replies
View Related
Jun 20, 2013
I created a basic excel weekly budget and would like to know how much money I have as of todays date. on the top row I have a date range from Sunday to Saturday, so it looks like this:
09-15 16-22 23-29
with the month manually put in above it.
then below I have income and expenses with a Overall below that, so basically what I want to is see the Overall value based on todays date, not sure how to do this with the weekly range and automatic current date(which is =TODAY() as far as I know) I have attached a photo as a reference.
Budget Picture.jpg
View 12 Replies
View Related
Dec 31, 2009
I have an 'existing results table' as per my attached sample.
I have had help previously from this forum to create lists of 'sold' stock within date ranges (tax year periods) and these are represented as 'sold list' in my attached sample.
I now need to create a list of 'unsold stock' for each annual tax year end date; i.e. populate my table with items that have been created before the end of the date range and that have not been sold by the end of the date range.
Please can someone show me the formula on my attached sample?
Please see my example in red.
I am working with Excel07 however my attached sample is in Excel03 because I couldn't upload an XLSX file.
View 8 Replies
View Related
Jan 2, 2014
I like to create "templates" for files that I work with on a reoccuring basis, just to make things simpler. The current template I am working on tracks items processed by day and is used for two reporting purposes; 1 totals the pay ending items processed so I have the dates laid out based on the 2 week period with formulas for that. The 2nd way it is reported is by the month. Since this is a template, only the first day of the first pay period for a calendar year needs to be entered and the rest of the dates populate based on that date. What I would now like to do is add columns for January - December monthly totals using a formula that will read something like "if column A (where the date is) contains 1/1/2014 - 1/31/2014, then sum up column L" and than copy that formula for the remaining months.
View 8 Replies
View Related
Sep 26, 2007
What i'm trying to do is select a range based on the date which happens to be in the first column of the range.
For example, I have a column (A) that contains dates and column (B) that contains names.
Its easy enough to select the entire range using
Range("A1:B4").Select
I need someting that will analyse column A and conditionally selct the range based on the date in column A (ie. it will only select the rows where column A has todays date).
View 9 Replies
View Related
Oct 13, 2008
I would like to have cell G3 automatically sum the values in Column B based on the start and end date parameters that are typed in the cells (E3 and E4).
View 2 Replies
View Related
Jun 2, 2014
I would like to know the function/syntax for determining the due dates given certain date ranges.
See attached sheet for the example : due dates example.xlsx
View 5 Replies
View Related
Jan 13, 2010
I have 2 columns, one containing a piece of data and one containing dates. I want to count how many times the data is in the first column, based on a specific date range in the 2nd column. I have attached a sample SSF with a better explanation inside it.
View 3 Replies
View Related
Jan 30, 2013
I have data in a spreadsheet similar to the below.
[Code] ....
What I need to do is created a named dynamic range based on the current month which will contain all rows/columns within that range. Ideally I need it to create the range for the current month so I can then use this as the data source for a pivot table.
I believe it is something to do with index and match functions?
View 1 Replies
View Related
Feb 3, 2014
I have a bunch of open files with dates in cell A3. I want to copy the range starting in A7 (the same range and size in all the open files) and paste it into my vbafile based on matching the date (in row 11). I have a spreadsheet attach with an example of what the code would do for Jan 1.
View 5 Replies
View Related
May 25, 2012
I am trying to add a gantt chart feature to a project summary worksheet. Ideally I'd like to search Col C (Start date) and Col D (End date) for all projects, and based on the earliest start date (ESD) in C and latest end date (LED) in D, repeatedly insert columns labeled with the value of the ESD (ie Feb 6, 2011), increment by 7 (1 week), insert the next column with ESD+7 and continue on until reaching LED.
View 4 Replies
View Related
Nov 17, 2006
I have one worksheet with 2 columns: A - Dates, B - User IDs. In another worksheet I would like to have two columns: A - User IDs and B - Count. The count in Worksheet2, column B would be a count of how many instances the user ID in Worksheet1 appears before or on a certain date. Example:..................
View 2 Replies
View Related
Feb 9, 2007
On sheet 1 in cell A2 I have a date (2/22/07). On sheet 2 I have 4 columns of data...column A has consecutive dates. I am looking for a code that will select the range that begins with the week beginning date of the date that was inputted on sheet 1 and the week ending date. For example, the code will select the range 2/19/07 to 2/25/07 (A20:D26).
View 6 Replies
View Related
May 3, 2008
Having trouble working out a macro for this...
Column B with dates and column C with values. I'd like to make another column with the cell value averages based on the date. In essence, calculating daily averages. In turn I will later be adapting it to go through the all sheets in all workbooks.
View 4 Replies
View Related
Sep 8, 2009
I have a monthly calender, with each month on its own sheet. I have a sheet named Holidays, which list the holidays. I have been able to use conditional format and highlight the dates in the months the holidays fall on, but I would like to highlight a range of cells below the date on monthly sheet. I have attached a sample of what I have and would like to achieve. I am not much good at using VBA, but would not be against using it either.
View 2 Replies
View Related
Sep 6, 2013
I have a date in a cell that when it is less than today() (so yesterday's date) would turn red and the 5 cells to its immediate right would also turn red, if the date is still current then it would be green and so would the right hand cells.
View 4 Replies
View Related
May 19, 2014
I have a VBA or function query.
I have a spread sheet with the following.
Column A contains asset names
Column H to JZ contains cash outflows for each asset
Row 17 contains a Month&Date value (ie. July2013)
What i want to do is, from another sheet, is to calculate the sum of all cash outflows for the asset (determined by a cell value) for a date range entered. In other words, if i say 1/7/2012 to 30/6/2014, it will look the asset up, and sum the value of cashflows, in colums that fall between those dates for that asset.
View 2 Replies
View Related
Aug 5, 2010
I am looking for code that can generate the report that a user selects.
For example the user clicks reports on the userform. The report userform opens up. The user checks the monthly report button and enters the last day of the month he wishes the report to be for. i.e 7/31/2010 and then clicks generate report. when executed excel creates a new workbook and creates two sheets. the first looks exactly like the master data sheet from the original worksheet but of course with the specified data. the second sheet will populate a summary of the data. I will address that after i figure out how to get the first sheet compiled.
View 1 Replies
View Related
Apr 2, 2009
I've got two sheet's in a workbook. The first contains data and is setup with dates in column A, day of the week in column B, and values going across by day in column's C through H. As an example it looks as follows:
View 5 Replies
View Related
May 14, 2012
I have one excel sheet. In the first column there are various Project Codes and in the first row there are various date. Cells in the matrix contains 0 or 1 value. If value is equals to 1 that means the project was on hold during this months and if the cell value is null of 0 (Zero) it means the project was active. For a project in a particular row there are several times a project went on hold and now I want a date range showing the project's hold duration. If project went on hold more than one time the result should also give different range for each time when project went on hold.
follow the link to view the excel file.
http://www.excelforum.com/attachment...3&d=1336994800
[IMG]http://www.mrexcel.com/forum/data:image/png;base64,
iVBORw0KGgoAAAANSUhEUgAABAAAAAMACAIAAAA12IJaAAAgAElEQVR4nOy9e3gT153/P99fd7vd77fxV+3udtO06fr3PL
[code].....
View 8 Replies
View Related
Jul 16, 2013
I would like to filter between a date range. the dates i need to filter between are in cells A1 and A2. this range contains the start and end of month. It is referenced from another sheet in the same workbook (='Job List'!C2).
i want to filter; greater than or equal to A1 and less than or equal to A2. How I can do it using the advanced filter method. My data range is C2:C9.
View 3 Replies
View Related
Oct 21, 2013
I have a range (D5:BU5) that I will have various dates in each cell. At the end of the row is a percentage block. It has the formula count=(D5:BU5)/70 which gives me a percentage complete. The fun begins where some dates are based off annual, semi-annual, and quarterly. What I want to do is be able to delete cell values so they are not reflected in the final percentage. I know there is a marco that deletes the entire row but that is too much I just want a target cell.
View 3 Replies
View Related
Apr 22, 2014
I'm compiling data from field reps that comes in a big spread sheet. I want to pull the rows out that fall into a certain date range. For example, if it falls between January and March. How would I do this?
Company 1
3/5/14
$54,000
Q1
Phase I
10%
Company 2
2/1/14
$16,000
Q4
Phase II
80%
Company 3
12/1/13
$18,000
Q2
Phase I
20%
View 4 Replies
View Related
Aug 10, 2014
I am looking to get a formula based on my spreadsheet attached
I want F collumn to add 5 days onto whatever date you put in there then correspond it to the matched date period in I2-I6 then apply the pay period from H2-H6 and put it in the G Collumn next to the date that has been input?
View 1 Replies
View Related
Jan 1, 2014
I would like to ask how can I get to work the index match function (if there's any formula other than this current function its fine) if i have a start date and end date as range date then another criteria for name to get my desired result. im having a hard time to explain so i attached a workbook so you can around it.
I found a formula and tweaked it but still cant get to work.
View 10 Replies
View Related
Apr 28, 2009
I have a spreadsheet with two columns - column A contains dates and column B has the corresponding data (always numbers). The data is not uniform (i.e there may be lots of entries in any given day and none over the next few). The data is always sorted in chronological order.
I want to set up a summary table to show the min, mean, median, max for each week. I have set up an array to calculate the mean, but I am having trouble working out a good way of gettting the others short of having a set of columns that manually define each cell...for instance =min(b3:b27) or =min(b28:b30).
View 2 Replies
View Related
Jul 14, 2014
I need to make an automated way of extracting a list of IDs and the associated cost by a date range, say 6/1/2015 to 12/31/2015, plus the starting letter of the ID. I need the data to then fill a form where it can be sub-totaled. There are several additional columns of data that are not relevant so I know I can create a pivot table and then filter the data, but then I need to copy and paste the filtered data into the form. Is there a way to do this without me copy and pasting everytime?
I added a test worksheet to show the data and form.
View 6 Replies
View Related
