Fill Table Based On Another Within Date Range & Cut Off Date
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.
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]....
In column A, I have dates; In column b i have security levels. I have made a table called "Security" it contains to columns, a list of security levels and no of years when each security level is required to be reviewed. the table is setup -
d1 e1 Restricted 5 etc
Example of data ie. a1 b1 c1 Restricted 1/06/2012 1/06/2017
What I am looking for is a formula to look up a1 "restricted". then lookup the security table and find "restricted" its value is 5 (years) then add the 5 years to date in b1, but place it in c1.
I need to create a table of dates which is structured as such:
A B 1/7/08 Yes 7/7/08 no 25/7/08 yes
Column A is date, Column B is a binary selection (yes, no)
I then have a separate sheet with rows corresponding to each date in July 08. I need a function which will look up the date for each row with the above date range and return the value of column B.
for example - if the date is between 1/7/08 & 6/7/08, it would return Yes. If the date is between 7/7 & 25/7/08 it would return No.
The above table is not fixed and can have many dates, with no pattern for the dates. I though of using a nexted IF statement but it would become a bit clunky.
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
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
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.
I have data going in to a small table which has some empty rows as that data is not yet available... My problem is, I need to sort this table in date order but with the date nearest to today's date at the top...
The sort function puts oldest at the top or oldest at the bottom which is no good for what I need...
I am having problems with calculations to be performed on date format. As in attached excel sheet, Start Date and End Date can be defined by the user. Once the user enters the dates, year 0 onwards are to be populated with values so that: (Also, not sure if I will need a button to initiate the calculations or a direct function will suffice)
year0 start = Start Date, year0 end = (Start Date +12 months) or (End Date), if End Date is before (StartDate + 12)
year1 start = year0 end, year1 end = (year0 end + 12 months) or (End Date), if End Date is before (year0 end +12)
year2 start = year1 end, year2 end = (year1 end + 12 months) or (End Date), if End Date is before (year1 end +12)
year3 start = year2 end, year3 end = (year2 end + 12 months) or (End Date), if End Date is before (year2 end +12)
year4 start = year3 end, year4 end = (year3 end + 12 months) or (End Date), if End Date is before (year3 end +12)
year5 start = year4 end, year5 end = End Date
If the dates are such that all years 0 to year 5 may not be covered, the years not used should be blank.
On sheet 1, I have dates in column A, then numbers under LabA through C. On sheet 2, I would like the contents of columns B, C, D to autofill when entered. However, I would like to display only the last entry. For example, Lab A (column B) is currently showing "7" in cell B6, if I enter a number in B7, I would like for sheet 2 to autoupdate cell B3 and display that new entry instead of the "7".
I have collected some data on economic factors for different countries. Unfortunately, the dates when I started to calculated my economic factors are different for each country (due to the data available to me).
What I would like Excel to have done is to take the date when I started to measure for e.g. country A (D3 ie 30/06/2007), copy it into column "I" (for country A, it's cell I3) and fill in the following months in the rows below (with always the date of the last day of a month) until it reaches 28th of Feb 2013. Then, it should go up to the next country (country B) take the starting date (D4, ie 31/07/2007), go to the last entry in "I" (ie I71) and paste the date in, fill in the months until 28th of Feb 2013, do the same for country C and so on.
I have started to code a VBA but I am unfortunately a beginner in VBA and totally stuck at the moment. My VBA code does paste in the months but for some reason, it also changes the starting date of the first month.
Moreover, I tried a workaround for the fact that Excel doesnt know when to stop; ie I introduced a "monthdiff" variable which should calculate the number of months between the starting date (which is variable and unique for each country) and the end date (which is always 28th of Feb 2013). At the moment, it only does this for country A.
VB: Set rng = ActiveSheet.Range("I3" & Cells(monthdiff, "I").Address)[SIZE=4][/SIZE]
I have tried to make this dynamic but have been unsuccessful so far.
Spreadsheet with data&code is attached.
VB: Sub Macro1() Dim mainrange As Range Dim rng As Range
I am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
I have a simple spreadsheet. A column for a persons name and 31 columns, one for each day of the month. I want to apply conditional formatting, either fill color or border color, to the date column of the current date when the spreadsheet is opened.
I'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.
Example
Initial Date :- 1st of January 2014
Frequency :- every 5 weeks
Current Date :- 9th of March 2014
Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)
I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)
I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 – October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc…. It should look like:
and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as I’ll have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.
I am trying to write a simple code that will take the last date in Col A and fill down until it gets to todays date.
Code: Sub Fill_down_date() Dim x As Integer lr2 = Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row For x = lr2 To 20 If Cells(x, 1).Value < Date Then Cells(x + 1, 1).Value = Cells(x, 1).Value + 1 If Cells(x, 1).Value
I have a table, which is a price list, with different prices based on different dates. For instance, the dates may be 1/1/04, 6/1/04, 7/1/04, 10/30/04, etc. I have some dates in another table, for instance, 2/2/04, 7/5/04, 12/20/04, etc. I want to lookup the date closest to, but not greater than, the last previous date posted. So, for the first date of 2/2/04, you would use the column with date 1/1/04 from the first paragraph. For a date of 7/5/04, you would use the column with date of 7/1/04. I think this is a simple VLOOKUP function, but not sure how to do a date compare. Also, the lookup has to do two things. First, it has to find the part number in the table, and then match it do the respective most recent date.
Table setup: Part Number ---- Date 5555555555 ---- 7/5/04
Lookup table: Part Number ---- 1/1/04 ---- 6/1/04 ---- 7/1/04 5555555555 ---- $65.32 ---- $68.52 ---- $71.52
I want to grab the price with the respective date from the first table. In this case, it would grab the price of $71.52 from the lookup table.
1 column in my sheet is a list of dates (affectively a calendar). I am trying to create a macro that will only display rows 1-5 and then current the current date and the next 30 days and hide all other rows.
The approach I have tried is to use “ADDRESS(MATCH(BS40,$B$1:$B$462,0),2)” equation to find the cell with todays date in it (Cell BS40 contains todays date). This was repeated for todays date +30. The problem I have is that although I now have the cell address’s located I cannot reference their values in a range.select
I have a couple of columns, once edited need to update a table in SQL. This is my code
[Code] .....
However it is not taking UK date formats, it is assuming it is an american format, I get conversion of varchar data type to datetime results in an out-of-range value. What should I add to change the format? The format is in UK on the spreadsheet seemingly....
When using a Pivot Table I regularly filter on known data specific to me i.e. WBS codes.
Column ‘A’ contains the WBS Codes which can be as many a 1000, however, I only need to filter out the 10 or so I require, and currently I uncheck all the records and manually scroll through the list to check the ones I require.
In order to speed things up I recorded a macro which works fine, however, If I need to check any new codes I would have to record the macro again, as I tried to edit the Macro and all it does is to un check all the other codes I don't need.
Is it possible to create a Macro that picks up a range of data that is then used to filter on. (i.e. only the records I require)
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.
The below code is working for >200702 and removes all years/months prior to this entered value. Unfortunately the code does not work when a user enters <200706, instead the code goes through to the “NonValidCriteria” prompt.
Sub HideByCriteriaYYYYMM() 'Declare variables 'SEE: [url] 'SEE: [url] Dim pt As PivotTable, pi As PivotItem Dim lMonth As Long Dim strCri As String, strCri1 As String, strCri2 As String Dim bHide As Boolean Dim xlCalc As XlCalculation
I have a report I am attempting to populate with data from a pivot table in another worksheet. Column A holds all the reference numbers (primary key), column B contains various start dates, and I want column C to contain all the payments made since the start date for each reference number.
The source data is a pivot table with Row = Reference number, column = transaction date, values = transaction amounts. This is an extremely large table, as I'm processing data from almost 1,200 cases, which each have around 20 payments spread over the last year, on completely random days. What I would like to do is build a formula in my report which looks up the records for the reference number from column A, and then adds up all the payments which have been made after the date in column B (and ignore any payments in the table which are before that date).
And to make things more complicated:
if an error is generated, it needs to return as 0, not #N/AThe report has the dates in UK format dd/mm/yyyy, but the pivot table has the dates in SQL format: yyyy-mm-ddThe pivot table is connected to a SQL database via ODBC and has to refresh every time it is opened.
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]
