Picking Last Data In A Week (Ended On Friday)
Oct 13, 2013last item in we 13-9-2013 is 13-9-2013
last item in we 20-9-2013 is 13-18-2013
last item in we 4-11-2013 is 30-9-2013 as shown in the attached
1.jpg
ADVERTISEMENT
Calculate Friday Based On Week Number
Jan 9, 2012I have a drop down box that chooses the week number of the year (This is based off of a series of data from another sheet).
I need some kind of formula that calculates the following Friday based on a week number. Say for this year (2012) The following Friday for "week 1" is 1/13/12.
(This is for payroll information and I'm trying to calculate the pay date based of of data from that week)
Find Pay Date From Date Sold (Friday Of Following Week)
Feb 17, 2014If a sale was made between 2/09/2014 - 2/15/2014 return the date of the following weeks Friday. In this case 02/21/2014.
View 3 Replies View RelatedPicking Data Out Of Different Columns?
Apr 2, 2014The purpose of the attached workbook is to calculate the average number of days a customer takes to pay their invoices. There are two tabs - Invoices and Payments. I am trying to calculate the number of days between Invoices "Sales Date" and Payments "Settlement Date".
The Payments tab is raw data exported from Attache financial system as CSV. It is the best I can get out of the system but the columns are all over the place.
I thought maybe from the Invoices tab I could search a row on the Payments tab to match the invoice number, find the cell containing the phrase "Invoice Number" then pick up the date from the following cell which contains the Settlement Date . This would be straight forward if the data from the CSV file consistently landed in the same column in the Payments worksheet, but it doesn't. As you can see, the phrase "Invoice Number" might appear in any one of several different columns.
Picking Up Selected Data
Feb 4, 2008I have Workbook with numerous worksheets,on one particular worksheet i require a formula/macro code that will pick up (say) those cells highlighted in red and then transfer that data set and the other selected information on that row to a another worksheet within the workbook.
E.g
Sheet 1 contains columns A to J;
Sheet 2 is where i require the information which is highlighted in red to be uplifted and pasted too.
so if Cell B2 contains the highlighted cell how would i uplift and transfer this along with data in cell C2,H2,I2 and J2 automatically to sheet 2.
SumProduct Not Picking Up Data
Aug 1, 2008I'm using the following formula to pick up data from another worksheet
=SUMPRODUCT(--(RawActuals!$A$1:$A$10000="jun"),--(RawActuals!$E$1:$E$10000=A236),RawActuals!$M$1:$M$10000)
It's picking up most of the data, but it's missing some - and it's to do with formatting of SOME of the data within the RawActuals sheet
212605Agency Staff - Professional & Other212701Staff Cost Tfr to Change Programme221112Property Rents - Passing Rent221310Hire, office/courtr'm/related eqpt
The 1st, 3rd and 4th lines are correctly picking up the data - but the 2nd is not. They are all set to the same number format.
Macro For Picking Out Trends In Data
Dec 21, 2011I am trying to write a macro that will automate the process of selecting rows from a data sheet. I want the rows to be selected based on the values in two columns. I want to find and select the row at the beginning and end of positions in a column where the values are equal. For a quick example, if my data looks like this below, I'd want to select the entire row for rows 2,3, and 6... I also need the row just before the value in both columns are equal.
row column11 column12
1 0 1
2 0 1
3 1 1
4 1 1
5 1 1
6 1 1
7 0 1
8 0 1
I have written this macro and it is not working... are there any obvious problems with this? This is new to me and I'm not exactly sure that I've used the correct commands. It also seems like LastRow is referencing 0 and not 12965 or whatever the last row of filled data is for the sheet.
Sub Deltadata()
Dim RowNum As Long, LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'Finds last filled row (should be 12965)
For RowNum = 3 To LastRow Step 1
[Code] ........
Picking Blocks Of Data To Copy From One Sheet To Another
Jul 22, 2014I have a series of spreadsheets that represent the rosters of teams in a league. At the end of the league season, some teams qualify to advance to a final event, and some do not. I start with a file of all the teams for that level of competition. This list comes from a program that creates it's output in an excel '97-2003 format (and I can't change the way the data comes out of this program). I modify the data a bit by removing data from merged cells and rename that worksheet a 'Raw Data'. In the past I have manually copied and pasted the data for the teams that advance to a sheet called 'Roster Data'. I am hoping there is a way to automate this part of the chore.
There must be 6 blank lines between the team names on the 'Roster Data' tab, but some teams have more members than others, so they are not spaced the same on the 'raw data' tab. Is there a way to select teams from the 'Raw Data' tab and have only the selected teams show on the 'Roster data' tab, AND have the spacing become uniform?
Picking Up The Month And Year From A Data Field
Jul 29, 2009i need to populate a field to show the month and year as follows: the data is entered in one field like this: 01/09/2006. i need another field to populate this: Jun 06. i have tried to use a few formulae but to avail. look forward to your response
View 4 Replies View RelatedSumif Function?? Picking Up The Data For The Value In The First Line
Mar 10, 2009I am using the following formuale to pick up a numeric value (from column G in Feb09 sheet: =INDEX(Feb09!G:G,MATCH($A10,Feb09!$B:$B,0))
Trouble is, the match it's doing in column b in Feb09 is listed twice in the sheet, but i'm only picking up the data for the value in the first line ... i think i need a sumif function.
SUMPRODUCT Not Picking Last Two Columns Of Data Table
Aug 12, 2013I cant seem to figure out why this sumproduct formula wwill not pick up the datatable on a separate tab. Data table is Column E to AA but it only goes to Column Y. If I try putting AA in - the formula returns the VALUE#. I thought it maybe the cell formatting but it does not seem to the problem.
=SUMPRODUCT((Worksheet!$C$3:$C$57>=$H$12)*(Worksheet!$C$3:$C$57
Open Ended Cell For Sum Range
Oct 14, 2005I have numerical data in column A rows 1:14.
I'm adding it up in row 15. Sum=(A1:A14)
Now I've been asked to insert more rows with data above row A14.
What can I put in my Sum formula in place of A14 so when I insert
a new row, any numerical data above it in column A automatically adds up?
Sum=(A1:?)
I don't want to have to go and change it to sum=(A1:A17) if I
insert 3 rows.... I have a huge file to create with sections like this. User
may have to insert new rows. They need to all add up.
Find Last Col In Open-ended Table, Then Sum
Sep 22, 2008I have a worksheet called Sales that could contain any number of columns of data. On another worksheet, I would like to find the last two columns and use a formula like:
Sumif('Sales'!C:C, c5, 'Sales'!D:D). (I have Excel 2007 so D:D is good)
The problem is how to change the C:C and D:D part to be the last columns.
I'm looking for something simple like this:
Sumif('Sales'!Secondtolastcol:Secondtolastcol, c5, 'Sales'!Lastcol:Lastcol)
Make Chart Series Open Ended?
Jan 7, 2013In a chart series, I have and end number for a row that I have been updating manually everytime I enter a new fuel tank fillup. Is there a simple way with the chart series to just say always go to the last data in the specified column?
See my example here, instead of the 137 in the series is there someway to just say "last data row"?
[URL]
VLookup Use With Open Ended Range And Array?
Jan 20, 2014I have a vlookup function that works for me in my workbook, when the data range and array is specific, but my data range will expand as will the array, as time goes on. I've used the "max" to produce the most recent results. Is there a way to accomplish this with an ever changing range and array? The most recent date will always be added to the bottom of column "A." I've used =VLOOKUP(MAX(A4:A25),A4:B25,2,0)
View 3 Replies View RelatedAVERAGE An Open-Ended (entire) Column
Jul 13, 2009I am trying to AVERAGE an Open-Ended (entire) column, while keeping the first 4 cells, "Out of the loop!"
Example:
=AVERAGE(E:E) less cells E1 through to E4, inclusive.
Average Of Open-ended Column, Within Given Intervals
Oct 2, 2006I need to average a column, without a fixed number of rows, and within an interval (and excluding zeros).
Example: Average of all numbers in the bracket 28-35, in column B2-B??
By this I mean that the total of rows in column B will vary from time to time, so in order to not having to manually change the number of rows each time, I need an "open-ended" formula, that also incorporates say 4 intervals (for instance 1-27, 28-35, 36-70, 71-100).
VBA To Copy Formats For Open Ended Range
Apr 23, 2014What I would like to do is copy the format of a range of cells (A2:O2) and paste that format into all the other rows of data beneath it. But my last row of data will change periodically, and I don't know how to tell Excel to find the last row each time. Here's what I was able to figure out on my own:
Code:
Sub CopyFormats()
Range("A2:O2").Select
Selection.Copy
[Code]....
Auto Open Macro To Find Correct Week Tab & Day/date In 52 Week Worksheets
Jan 19, 2010I am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).
Sub Auto_Open()
week(1) = "WK1"
week(2) = "WK2"
week(3) = "WK3"
week(4) = "WK4"
week(5) = "WK5"
week(6) = "WK6"
week(7) = "WK7"
week(8) = "WK8"
week(9) = "WK9"
week(10) = "WK10"
week(11) = "WK11"
week(12) = "WK12"
week(13) = "WK13"
week(14) = "WK14"
week(15) = "WK15"
week(16) = "WK16"
week(17) = "WK17"
week(18) = "WK18"
week(19) = "WK19"
week(20) = "WK20"
week(21) = "WK21"
week(22) = "WK22"
week(23) = "WK23"
week(24) = "WK24"......................................
Auto-updating Formula For Week Over Week Change?
Dec 11, 2013I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.
What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.
Delete And Insert Record Week On Week
Aug 6, 2008I have a comparison model that looks at two weeks of data. I am trying to get around the deletion and insertion of records week on week. With the code below, I can currently find and correct the deletions and insertions to the list, and then resort the list so that the comparison will work.
Sub CheckForNewProjsRemovedProjects()
Dim MyCell As Range, oCell As Range, NewCell As Range
Dim Rng1 As Range
Set Rng1 = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
' Columns("B:B").Select
Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"C1"), Unique:=True
For Each oCell In Rng1
For Each MyCell In Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
If MyCell.Value = oCell.Value Then................
Week Number Caluclating Hours For Week
Mar 6, 2010I have a running time sheet daily. It has 2 columns for Labor and 2 columns for travel
i.e. travel From / To 1300-1400 calculate 1 hour then travel home 1600-1700 1 hour this is calculated by the date entry 01/02/10 I have another calculation that tracks by the date i.e. 01/02/10 then Next job which all works fine.
The problem becomes how do I calculate a weekly total labor and travel by the date So added another column called weekly hour’s labor and use the Weeknum to determine which week is which day/date so the first Monday in January 2010 is week 2
2 problems
Having many multiple day / date entries are the same date x 7 days Monday –Sunday
(Relies on the date entered and the weeknum) 01/03/10 each line is complete however the dates carry over as does the time
When trying to calculate each row x 3 same date time then the value will be incorrect I need to calculate
Say 9.5 hour labor from the date 01/03/10 not 28.5 hours and then calculate the total weekly hours
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
i do have work and travel times for each job on the same line (separate columns) but I display the total here by date to summarize the totals
i have tried sum products and sumif to avail. I am using Windows XP SP2 with MS Office 2007
how do i calculate weekly hours by date and weeknum ?
Total Work per day
Total Travel per day
Daily....................
Reminder On Every Other Friday
Dec 20, 2007Every other Friday, I need to submit my biweekly work hour sheet to manager. For example, as 12/21/2007 is Friday and pay day, I need to submit my sheet. Next time is two weeks later, 1/4/08, and on.
12/21/07 is the first event.
I want to create a conditional format, or workbook events, or worksheet events to remind me on that Friday when I open that book or activate that sheet.
Returning The Date For Last Friday?
Feb 12, 2008how to write an excel workbook function that will return last friday's date?
For example: If Today is 2/12/2008, it will return 2/8/2008 as the date
Change Non-working Day To Friday
Feb 28, 2005how can i change nonworking date from saturday&sunday to frinday&Saturday
View 11 Replies View RelatedDATES: Identify Every Other Friday
Apr 7, 2009I have a column that has dates going vertically down from 1/1/2009 - 1/7/2010. I need a formula that will return the date for every other Friday beginning with the second Friday in January 2009.
View 6 Replies View RelatedGet Date Of Last Friday In Month?
Mar 25, 2012If there's one thing I really wish I understood more in Excel its how to use formulas to calculate days and times.
I'm looking for a formula that will give me the date of the last Friday in a month based on another date. I have the formula below at the moment that works quite nicely for March 2012 but not for any others. It's getting the 5 at the end to be more flexible that's got me stumped.
=DATE( YEAR(H12), MONTH(H12) + 1, 0) - (WEEKDAY( DATE( YEAR(H12), MONTH(H12) + 1, 0), 2) - 5 )
Calculating Friday Date
Aug 7, 2006I have dates listed in column A indicating the day an action was performed. In column B I need to have the date in column A changed to a Friday. If the date in column A is not a Friday, then the date in column B needs to fall back to the previous Friday. For example, if the date entered in column A is today, Monday August 7, then the date in column B that I would be Friday August 4. Can anyone think of code that determine this and place the value in column B? Currently I use vlookup to go to another sheet where all the possible dates are listed with their respective Fridays, but this slows down the calculation worksheet as the file is quite large.
View 9 Replies View RelatedReturn Last Friday From A Given Month
May 23, 2007Re: Return last Friday from a given month
This will calculate the last Friday of the month in which the date in A1 falls:
=DATE(YEAR(A1),MONTH(A1)+1,0)+MOD(-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-2,-7)
HTH
TJ
actually this thread is closed. I want to convert this into UDF but not able.
Determining Date Of 2nd Or Last Friday Of Any Month?
Sep 14, 2008Without VB, is there a way to write a formula which when given a certain month, will output the date of, let's say, 2nd or last friday of the month?
View 14 Replies View Related