Creating A Date Range Trigger
Jun 5, 2007
hi i am currently trying to construct a date based maintainance sheet
i will try to explain what im trying to do
in one cell date is inputted
the next cell automatically adds +30 to generate next test date
what i am trying to do is get the sheet to
there are 4 testers so its a 3 month cycle for each ie
tester 1 jan
tester 2 feb
tester 3 march
tester 4 april
tester 1 may
tester 2 june
you get the picture? i hope
so if
cell A1 todays date
a2...................................b2......................................c2
21 jan.............................21 feb................................tester 2
inputed ...................... =A1+30
and date
formated
cell
i am trying to get the formula to tell me after the inputed date has been changed who the next tester is
i have tried using the IF command but it wont let me specify a date range ie 1 jan to 31 jan (or in the specified date codes) date range only one specific date with >= or <= which doesnt quite work properly
View 9 Replies
ADVERTISEMENT
Jul 1, 2014
I have the code below that is two separate activities and I want to change the second activity from a cell trigger (Set KeyCells = Range("K42:AD42")) to a button trigger. I need to first to remain unchanged.
I'd be ok if this was just one macro that I could assign to a button but because its two and I need to write the second's to clicking a button I'm over my head.
Its occured to me while writing this that because it'll be a range of buttons I'll probably need to make each one an individual code? Is this the case? If so I may have to just keep this as it is.
View 2 Replies
View Related
Jul 15, 2014
I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.
View 1 Replies
View Related
Mar 20, 2012
I have a start date, and an end date for a promotion. However, I am trying to create some unique keys for a VLOOKUP, so I can combine the data I have by day, with the data I have for promotional start/end dates.
So, simple question, if I have a start date and an end date, is there was way (either using excel or vba) to pull out all the dates within that range and display them on a spreadsheet?
For example, I have 21/01/2012 and 28/01/2012 as the start and end dates. I would want to display 21/01/2012, 22/01/2012, 23/01/2012 etc.
View 3 Replies
View Related
May 20, 2013
I am preparing a list of calibration items that require yearly calibrated, how can I set the date and prompt me example 1 month ahead when the item is going to due soon.
View 3 Replies
View Related
May 21, 2009
I am trying to work out a formula calculating dates.
basically a piece of work / report is due for completion twenty working days from a trigger date. And I need the formula to calculate this due date. Im using the workday function, which calculates a date using the number of working days given after the trigger date.
this would work fine, except that the trigger date needs to be included in the calculation (inclusive). I have tried changing the value for the number of working days to nineteen, which would logically give the right answer (20 days including the trigger day). BUT the bloody thing doesnt work, because the trigger date can be on a weekend or holiday!! so then it calculates 19 days (as it should) but the result is wrong because the trigger day isnt included (as its a holiday/weekend).
View 9 Replies
View Related
Mar 20, 2014
Trying to do a linkback from another post located here but not having much luck doing it: [URL]
I'm working with 2 date columns and trying to filter a view to only include projects with dates within 3 months of today's date.
I've attached a current working file of the data and the end result i'm hoping to achieve via a macro of some sort.
I've manually got it to work via formula by inserting 2 additional columns (highlighted yellow) which determine if the dates "YES" fall in this 3 month time frame of "" blank if not.
create a macro which does all of this automatically without modifying any columns if this is possible
View 5 Replies
View Related
Mar 14, 2013
I have a custom email creation template I am merging with another version. The problem I am having is wrapping my head around not only selecting a range that is offset from ActiveCell (column 6-9) but seeing if there is an "x" in that range which is normally blank. My previous attempts identify the "x" but adds the text every time it is found. (Each column is a flag for an email bullet and they can have all four bullets in the email where I only want the text included ONCE if they have ANY bullets included). I use the range because I do not want the text included if none of the bullets are used.
Teh StandHTML then gets used in the body of the email like other HTML items I use
The email is generated using the ActiveCell.Offset to insert special text, emails and routing and has weathered alot of changes over time.
Dim Myrange As Range
Myrange = Range(ActiveCell.Offset(0, 6), ActiveCell.Offset(0, 9)).Select
If Myrange = "x" Then
StandHTML = StandHTML & "Important Text"
End If
View 5 Replies
View Related
Dec 22, 2008
I am trying to build a selection for a procedure to use.
View 2 Replies
View Related
Jun 23, 2009
I have a HR database that has user details on and successors so that if anything happens to a user (promotion or sick leave) then my database can identify who can step into that role temporary or permanently.
As i complete the cells at the end i would like to add a completed date then excel will count the days until it reaches a certain day and will change the text to either Review due in 1 week or review due in 3 weeks, 6 months review due, Overdue.
This way i can identify who i need to email so i can get up to date information on that role so if the users has changed position i can update accordingly...
View 9 Replies
View Related
Jul 28, 2009
I am trying to create an Excel spread sheet that keeps track of employees safety tickets and the dates they expire. Is there way to have a cell highlight to a color warning me 30 days prior to the due date listed?
I tried a conditional formatting as suggested in
[url] but that does not work.
I am using Microsoft Office Excel 2003 (MS Office Standard)(SP3)
View 9 Replies
View Related
Dec 9, 2013
Is there a formula that can be used to convert a manufacturing LOT Number to a Date? For example, the first six digits of our Lot Number are the MMDDYY followed by an item description (120913DKP-01). We are trying to pull the 120913 portion out of the lot number to equal a Date of Manufacture of December 9, 2013.
View 4 Replies
View Related
Sep 10, 2009
What I have is a double quoted, comma delimited text file that is created from an external application, every now and again we need to open the file to make corrections and this is easier if we open it via Excel using the delimiter options, the problem is when saving the file as a csv it removes the double quotes, however I have found some vba coding that adds the double quotes back, which works great, but it changes the format of the date fields from what the original file had from 09-Aug-2009 to 09/08/2009.
I suspect this may be because the file is opened before the double quotes are added and excel changes the format.
Is there any code I can add that will stop the date format from changing ...
View 12 Replies
View Related
Jan 18, 2009
I am using the VBA Chart Control and I have the following instruction;
Me.ChartSpace1.Axes(ChartAxisPositionEnum.chAxisPositionCategory).Numb erFormat = "mm/dd/yy"
The chart is a "chChartTypeLine". the problem is that i cannot change the format of the X-Axis to a Date (mm/dd/yy) format no matter what I try? I keeps wanting to keep it as "General", here is the code;
Private Sub CommandButton1_Click()
Dim ser As ChSeries
Dim cht As ChChart
Dim MinValue As Double
Dim MaxValue As Double
MinValue = Sheet1.Range("D2").Value + 10
MaxValue = Sheet1.Range("A2").Value
View 9 Replies
View Related
May 25, 2014
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.
View 1 Replies
View Related
Jun 30, 2009
I'm trying to create a range between two text values; for example:
Equity
Stock1
Stock2
Stock3
Equity Total
Fixed
Stock1
Stock2
Stock3
Fixed Total
How can I name a range from "Equity" to "Equity Total" and from "Fixed" to "Fixed Total" in VBA?
View 9 Replies
View Related
May 1, 2014
I have a employee travel spreadsheet with air travel dates in two columns. I want to be able to create a rule that will highlight all the cells in a row if the date range on that row fall includes today's date. if someone is traveling in the event there is an emergency and I need to know if they are traveling that particular day. I have attached a sample spreadsheet.
View 3 Replies
View Related
Jun 29, 2014
I want to find the number of similar values contained in a column based on a date range I specify.
I have a data set that's formatted like the following (you can see the full data set at [URL] but I've hidden the irrelevant columns and highlight the relevant ones)
[Code] .......
I want to use this data to create two types of graphs. One showing the ratio including all values in the Status column. There are 4 total (Open, Lost, Won, Won-Job Cancelled). I want another which excludes the "open" status from the ratio figure. I've create some images demonstrating what I want to accomplish at [URL] .....
Additionally, I want to be able to specificy date ranges for these charts. Specifically 0-30 days from todays date, 30-90 days and older than 90 days.
I want to accomplish this using Google Sheets (the latest version) and know how to use the graph total. It's just creating the data the way I need it which is proving to be the most challenging.
View 1 Replies
View Related
Dec 30, 2009
I am playing around with creating a calendar based on a date (month) I enter into a cell. I am doing this with a simple =date+1.
I have a sample attached. Only problem I am having is that for months with 30 days I don't want the last day to show the first of the next month. But I can't simply remove that as if I entered a month with 31 days I would need it to show the 31st.
View 4 Replies
View Related
Oct 11, 2008
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:
Monday Date:
10/13/08
October 19 – October 25
Sunday 19
Monday 20
Tuesday 21
Wednesday 22
Thursday 23
Friday 24
Saturday 25
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.
View 2 Replies
View Related
Aug 19, 2014
I'm trying to create 3 different named ranges which are of alternate cells in columns. Each column runs from row 3 to row 106 and each named range covers 5 columns. Therefore each named range needs to contain 260 cells (52 cells per column x 5 columns).
Apart from the fact that you can't deselect a sell by Ctrl+Right Clicking on it, making a selection of the relevant cells is proving impossible.
For example I carefully selected all 260 cells for my 1st named range and entered a name. However when I then go into Name Manager I see that loads of cells haven't been selected at all and instead some cells in some columns have been but with huge gaps between them. If I try to then add cells my Ctrl+Clicking on them I find that I can add some but then one of two things will happen. I'll either click on one cell, the same cell every time, that deselects everything and I have to start all over again or when I try to save the new range I get a message saying the formula is wrong.
I've also tried typing in the names of the cells manually but the same thing happens. Annoyingly I've manually created a formula with all the cells I need in Notepad but even though the option to Paste is there, it won't let me paste into the field!
Is there a limit to the number of cells you can have in a named range?
Is there way of editing the name range accurately?
Is there a better, easier way of selecting all the cells for a named range?
[Code]....
View 4 Replies
View Related
May 18, 2013
How would I create a variable range with:
Sheets("output").Cells(I + r, 1):Sheets("output").Cells(I + r, 9)
View 3 Replies
View Related
Dec 27, 2009
I have a text string concatenated from an array of about 3,500 elements. The concatenation looks something like the following:
sRange = "$A$3,$A$7,$A$8,$A$13, ..."
When I try to set a range with the said string: MyRange = Range(sRange), the system throws an error.
I've seen a few methods in help ("Union" for one) that accept up to 30 arguments. I'm unable to find anything in help that states a limit for MyRange = Range(). It appears that 30 may be the magic number for everything.
I have tried using "Union" and building the range one element at a time from the array. That works, but it's incredibly slow.
Is there any way I can pass my array as the argument directly? Something like: MyRange = Range (MyArray).
View 9 Replies
View Related
Oct 29, 2006
I am generating Barcodes in Excel with the Barcode Symbol Code 39.
When i enter the desired number in the cell, the Barcode for the entered number gets automatically generated on the previous cell.
eg: Barcode (A3)
Number (A4)
Empty cell
Barcode (A6)
Number (A7)
Now i want to generate a sequence of barcodes starting from so and so number and ends in so and so number.
Now the way i do is manually enter each number one by one which is a tedious work.
View 9 Replies
View Related
Mar 13, 2013
I have a strange problem I cannot solve in that I'm trying to create an auto filter that returns records based on a date variable.
If I set the code to equal the date variable, it returns information. If I set the code to return records AFTER the date, it returns nothing.
Code to return records on the date:
Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:="=" & startdate
This returns data, however if I slightly amend it to get records on or after the date:
Code:
Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:=">=" & startdate
It returns nothing. Is there a syntax problem in my code.
View 1 Replies
View Related
Mar 6, 2013
I'm trying to create a border for a range using R1C1 and get an error.
VB: Sheet11.Range(Cells(y, 1), Cells(y, 5)).BorderAround Weight:=xlThin
If I do not use R1C1 it works...
VB: Sheet11.Range("A18:E18").BorderAround Weight:=xlThin
View 2 Replies
View Related
Dec 29, 2009
For work, I'm doing a project for Quality Control. I'm looking to create a Bar Chart (horizontal bars) with 2 "vertical" lines to indicate the range of acceptable values
Attached to this Post is a *.pdf that (hopefully) further explains what I'm looking for.
View 4 Replies
View Related
Apr 8, 2012
I am importing a text file and plotting some of its contents versus time. The file provides the end time and the step interval. I know that in Matlab you can define a set of values with the start, end, and interval, but I'm having a hard time figuring out how to do this with VBA. I have toyed with fill series, but no luck there.
Specifically, the file I'm reading in (a relay event file with voltages and currents) gives the maximum cycles as 15 and the samples per cycle as 16. Therefore, for each voltage or current sample, I need the time to show as 1/16, 2/16, 3/16, ..., all the way to 15 cycles. I have tried to manually do this and record the macro, but after I put in a few x/16s, when I try to extend the formulas down the rest of the cells, instead of continuing the trend (4/16, 5/16, 6/16, etc.), it repeats what I put in first.
View 1 Replies
View Related
Jun 4, 2012
I am trying to create a large range composed of smaller ranges. Some of these ranges may overlap so I want to make sure when I add cells to the range they don't already exist. I know how to make sure the same value doesn't exist in a range, but not the same cell.
View 2 Replies
View Related
Mar 29, 2013
I am trying to create a range with visible cells after filtering based on a criteria. But, facing some wierd problem with setting correct range with visible cells. Here is the code.
Code:
xlsDynRange = xlsWorksheet.Range("A1:G500")
xlsDynRange.AutoFilter(Field:=1, Criteria1:=intEmpid)
xlsFilteredRange = xlsDynRange.Range("A1:G500").SpecialCells(Excel.XLCellType.xlCellTypeVisible).Cells
Here, xlsFilteredRange.Cells.Count returns correct values. For example, for a particular filtered criteria, if there are 6 rows, it returns 42 (6 rows * 7 columns). However, xlsFilteredRange.Rows.Count always returns 1 (header row), though there are 6 rows. How can this be fixed?
View 3 Replies
View Related
