Applying Date Range
May 18, 2009
To explain I have two sets of merged data. On one fixed column I have two different sets of peoples age
1 - by age grouip eg 25 - 34
2 - By birth year so 01/02/1983
I know that person who is born between 1975 and 1984 would fall into the age group 25 - 34
I want to convert all the birth years into age groups. Its a lot of data (30,000 rows +) in random order
Does anyone have any idea how I would set this up, as I really want to add "age group" to a pivot table I have.
View 6 Replies
ADVERTISEMENT
Jan 24, 2007
I'd like to enter dates without having to use the forward slash symbol between the day, month and year values. I've read a post by Dave Hawley explaing how to do it, but I can't get it to work for me. There must be some step that I am not aware of.
View 9 Replies
View Related
Feb 17, 2014
i built a form and in the date section i have a date picker and once i fill the form out i have a button that saves all the data on another sheet like a database, what do i need to do to make it were it adds the date to the database itll add all the other data but cant seem to figure out how to make the date display
View 3 Replies
View Related
Feb 6, 2007
all i need to write is a code which syncronizes with my update button, which i don't know how to write....whenever i click the update button, all my files will be updates, and at the top elft of the screen would show the time and date of my last update.
View 6 Replies
View Related
Apr 3, 2014
I have a macro which sorts data within a cell. This is working perfectly fine but the only problem with this is that it works only for a single which is selected. How can I apply this macro to range of cells I select using a mouse.
View 3 Replies
View Related
Feb 6, 2008
I have 10 ranges that if the value in a cell falls into one of those ranges, a calculation needs to occur. I think I need to use a macro which i have set up to run off a button click, with a do loop in it, but not sure how to get the loop to move down the range?
for example:
Cell Reference(s):
D
1 250
2 700
Range:
A B C
1 0 150 75
2 150.01 300 125
3 etc.. etc.. etc..
Formula:
=if(and(d1>a1,d1
View 9 Replies
View Related
Apr 4, 2008
Im using a commandbutton to apply a discount to a range of cells. I want to give the user two options based on either 18 months or 24 months.
So I was thinking somthing like, when the command button is used have a msg box asking them which term 18 or 24 than apply the discount to Range H25:H34.
I want to set the discount % to Range P20 for 18 months and P21 for 24 months.
View 9 Replies
View Related
Jun 29, 2006
I was able to get my four conditional statements in VBA to work. This covers one complete row of data (week), but I need it to cover an entire year. Rather than cut and pasting 365 times (and some UGLY code at that), I have been trying to figure out how to add a loop or something to cover a range of different rows. Basically Columns B though V are good for row 21. Now every 14th row I need this routine to work for (B35:V35, B49:V49, etc) for 52 weeks. I was playing around with the idea of two for statements taht would count shifts (21 = 7 * 3) and weeks (52). Then step the rows at 14 and the columns at 1 (Rows = Rows + 14) for 52 iterations. I'm sure there is a better way to distribute this routine.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim changeRange As Range
Select Case Mid(Target.Address, 2, 1) 'Column Letter
Case "B"
Set changeRange = Range("B21")
Case "C"
Set changeRange = Range("C21")
Case "D"
Set changeRange = Range("D21")
Case "E"
Set changeRange = Range("E21")
View 9 Replies
View Related
Dec 26, 2009
how can i do the following using VBA
making each cell in column A added to each cell in column B and the result will be in the column C
for example
c1=A1+B1
c2=A2+B2
c3=A3+B3
... etc
i know i can do that simply without vba code but I just want to use this method to implement more complex formulas .
View 9 Replies
View Related
Mar 3, 2014
I need to input a formula that will produce a "yes / no" result if a "0" value appears consecutively six or more times.
Using the date below (ranges A1:Q1, A2:Q2 and A3:Q3), I need cell R1 to read "yes", as there are 7 consecutive "0" values. Cells, R2 and R3, however, need to read "no" as there are less than six consecutive "0" values in rows 2 and 3 respectively.
A
B
C
D
E
F
G
H
I
J
[code].....
View 8 Replies
View Related
Jul 10, 2012
I have an Excel 2007 Workbook which is refreshed by a Web Query. I have dynamic named range defined as "Manager" which I've confirmed is correctly identified. This range includes only one column and is formatted as text.
When I try to reference this range in my conditional formatting "refers to" formula, all cells are recognized as blank even though the range clearly contains many cells that are not. For example, there are 90 records containing the initials "PD". If I use the formula =Manager="PD" in my conditional formatting, nothing changes. If I change it to =Manager="" then ALL records are formatted, including those that are not blank.
Stranger still, if I enter the formula =COUNTIF(Manager,"PD") into and empty cell in my worksheet those 90 records are counted correctly. Which leads me to believe it isn't about the data. Conversely, =COUNTIF(Manager,"") returns the correct count of only cells that are, in fact, blank.
I've tried using the OFFSET formula defining my range in place of the name itself for my conditional format formula to no avail.
View 2 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
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
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
Jun 17, 2008
I have a table of data with names across the top and number of weeks down the left. The table lists amounts of money those names earned during the respective weeks. It looks something like this:
Mike Dan Bill Carl
400 500 600 700
800 900 1000 800
700 600 500 400
In a seperate worksheet within the same workbook, I have 3 columns of data. The first column is a name. The second column is a criteria. In the third column, I am trying to look up the value of 8 specified weeks of earnings of the name I put in column 1 and multiply those earnings by a percentage based on the criteria in column 2. Then sum the products.
I have a table of the 8 percentages I am trying to apply. There are 7 different percentage applications.
So if I am looking up Mike's earnings during those 8 specified weeks and the criteria in column 2 is "B", then I want to apply the appropriate column of percentages to those 8 looked up earnings, Multply them and then Add them.
Up until now, I have been trying to use some hybrid of HLOOKUP, SUMPRODUCT, and IF.
View 9 Replies
View Related
Jan 9, 2014
I am trying to update a project tracking sheet at work and I am having trouble. I have a list of construction projects (approx 130) along with details on the project, one detail being % completed. I am trying to create a summary of the projects at the bottom of the page, showing the total value of all projects, according to the % completed.
<10% is ANTICIPATED
= 10% is AWARDED
>10% <90% is IN PROGRESS
>90% is COMPLETED
[code]....
View 8 Replies
View Related
Feb 25, 2013
Using VBA in a code. I was using this same code for another workbook before and it worked fine.
[Code] ...
The filter works, but it is not applied. after the macro is run, I need to click "OK" on the filter for it to apply.
View 7 Replies
View Related
Feb 20, 2012
I have an excel sheet where I do a simple price-discount calculation for a product. Please check the file at:
Code: [URL] ........
A2 shows the price, B2 shows the discount and C1-L1 shows the number of units sold. C2-L2 is where I want to calculate the earnings based on the formula I have in C2. I want to apply the same formula to the entire row so that I can quickly see the earning for any number of units sold.
View 3 Replies
View Related
Jul 8, 2013
My issue is my For Each command is not grabbing the next worksheet and applying my code. Here is my code:
Sub Test()
Dim ws As Worksheet
Worksheets("Report").Activate
For Each ws In ActiveWorkbook.Worksheets
With ActiveSheet.PageSetup
.LeftFooter = "&D"
.CenterFooter = "Test"
.RightFooter = "&P"
End With
Next
End Sub
View 5 Replies
View Related
Feb 8, 2014
So right now I have a formula that I'm using in column "C":
=IF(B4="","",IF(AND(B4=22),"IN RANGE","OUT OF RANGE"))
So basically column C tells you if the number in the B column in within the range of 22-30. Now I want to write the formula in column D and make it apply to columns B and C.
So cells in columns B and C will have a number value in it and I will make column D where the formula is. There will be a number in B or C, but never at the same time. I can't figure out how to alter the formula to do this.
View 3 Replies
View Related
Mar 3, 2008
I use the If - Then statement in VBA to determine a condition which works perfectly fine for the particular cell I reference to, but i need it to apply to all the cells i.e. cells A1:A10?
Sub MACRO1()
If Range("A1") = "PAYE" Then
Range("B1").Formula = "=C2 * .128"
End If
If Range("A1") = "LTD" Then
Range("B1").Formula = "=(C2 - 100)* .128"
End If
End Sub
Sam
View 9 Replies
View Related
Aug 22, 2009
have a code for restricting printing until some cells are filled; however the code is been applied to all the file. I need to apply it to one one sheet.
Thanks for your help.....
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
If Application.WorksheetFunction.CountA(.Range("A11:K11"), ("A13:K13"), ("A16:K16"), ("A19:I19"), ("J18:K18"), ("A22:K22"), ("A25:K25"), ("B63:B64")) < 8 Then
MsgBox "Please Complete Information"
Cancel = True
Else
'Allow printing
End If
End With
End Sub
View 9 Replies
View Related
May 3, 2006
I have cell A7 Merged and need info in that cell. Example of what I want: Say I click on cell A1 and it is the color Pink, but has the letters blue in it. I would like it to display the word "Pink" in cell A7 not the number. Well I would like when you click on any cell with colors, that it will display the color in Cell A7. When I try different ways all it gives me is the number in the last cell of the square not the color and when I click on other cells it will not give me that cells info.
Private Sub CommandButton1_Click()
Dim x, rng As Range, r As Range
Dim myList
Dim row1 As Integer
x = Application.InputBox("Enter size of square: 2=2 by 2, 3=3 by 3, or 4=4 by 4", Type:=1)
Set rng = Range("a1").Resize(x, x)
myList = [{1,2,3,4,5,6,7,8;6,11,3,10,13,16,38,53;"Brown","Pink","Grey","Purple","Green","Red","Blue","Yellow"}]
rng. CurrentRegion.Clear
Randomize
For Each r In rng
x = Int((8 * Rnd) + 1)
With Application.WorksheetFunction
r.Interior.ColorIndex = .HLookup(x, myList, 2, False)
r.Value = .HLookup(x, myList, 3, False)
Range("A7").Value = r.Interior.ColorIndex
End With
Next
With rng
.ColumnWidth = 10
.RowHeight = 50
With .Font
.Size = 14
.Color = vbWhite
.Bold = False
End With
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.Weight = xlThick
End With
End Sub
View 9 Replies
View Related
Jun 14, 2007
I need to cut off addresses by 30 characters. I am doing this using the MID function. However, being a newb, how can I apply this function to say D4, D5 all the way to D899, without copy and pasting for 800 times? I did search this first, and couldn't find a resolution.
View 4 Replies
View Related
Jan 2, 2014
I have a table that looks like this (its basically a historical data of a stock exchange):
Date
Index
January 4, 2010
[Code]....
The List continues till the current Date.
I want to calculate Average Index Values of a Date of each month within a Date Range.
Example: Calculate Average Index Values for 3rd of Each month from 1st Feb 2010 to 3rd Jan 2011. Formula should calculate Average of the Index Values for 3rd Feb 2010, 3rd March 2010, 3rd April 2010, 3rd May 2010, 3rd June 2010, 3rd July 2010, 3rd Aug 2010, 3rd Sept 2010, 3rd Oct 2010, 3rd Nov 2010, 3rd Dec 2010, 3rd Jan 2011.
Both the Date and the Date Range is variable. Also, the Index Value for selected Date of one or more month may not be available as that being a holiday. In that case, the formula needs to use the last available Index Value before that Date. e.g. If Index Value for 3rd Oct 2010 is not available, system will use the Index Value of 2nd Oct 2010.
View 1 Replies
View Related
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
May 26, 2014
i am 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 4 Replies
View Related
Apr 21, 2006
Let's say I have thousands of employees, but I need to determine who worked for me during a particular date range, and all I have to go on is their start date in one column and their end date in another column.
If:
A1 contains beginning date of employment
B1 contains ending date of employment
C1 contains specified beginning date (criteria)
D1 contains specified ending date (criteria)
View 4 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
Feb 14, 2013
Cell B1 is to display money value dependent on what item has been selected from drop down box in Cell A1
View 2 Replies
View Related
