# VBA Formula; Sum Up Part Of A Column Based On A Date Range

Feb 24, 2009I am trying to write a formula with some variables passed into it. i want to sum up part of a column based on a date range (i've got the range already).

View 3 RepliesI am trying to write a formula with some variables passed into it. i want to sum up part of a column based on a date range (i've got the range already).

View 3 RepliesADVERTISEMENT

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 Relatedis there a formula that will look at A2 and if the date in that column shows May 5, 2013, then in E2 it enters June 1, 2013? I would like E2 to automatically populate the first of the following month of A2.

View 7 Replies View RelatedAny way to construct a formula in excel that will look at a reference in one column and find the latest date from the data in an adjacent column for that specific reference?

Below is an exctract from a much larger sheet of the columns in question.

The result in the last column should be 21/05/2014 for anything with D.O.001 in the second column and 15/05/2014 for anything with D.O.002.

Date Decision agreed

Disposal Order

Latest Decision date for D.O.

06/05/2014

D.O.001

[Code] ........

I have a form in which users will manually enter a date in Column A. I would like to create a formula in Column B which will add 4 days to the date based upon Column A. However, the 4 days should only be added to a select set of dates which I would like to specify. If the date is not found in this select set of dates, then the result in Column B should be the same date as Column A.

View 3 Replies View RelatedI have a formula that counts if a date range is present. However I need to change it to count another column only if that date range is present. For example a17 a50000 the user will enter the date of the order. and in column B has the order number. I want the formula to count the order numbers for a data range in column A.

Here is what I have but it is counting the dates in col A not the order numbers in B?

I want to define a dynamic named range based on the last date in a range (AE4 down). Unfortunately there are gaps in this range where no date is in a cell.

View 5 Replies View RelatedI 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?

i have a sheet for monitoring sickness. i have used a macro before which automatically selects a range and emails it to recipients when i press a button. what i would like to do is have excel automatically choose the range for me based on today's day. in the attached example i would like column "A" copied along with the 5 columns before today's date, today's date column and also the 5 columns after today's date. paste the lot into an email and send. (i have highlghted the parts i would like copied and pasted into an email based on today's date (21st july).

View 4 Replies View RelatedI'm trying to create a list that references an existing data set where I have staff listed month by month and based on today's date, imports only if there is data in that column. I have figured out how to check if the cell is blank or not, but what I want to do now is change the cell that is referenced in the formula based on the date. Here is the format of the spreadsheet I'm working with:

A

B

C

D

E

F

G

H

[code].....

In this case, the "Team 1" and "Team 2" and "Team 3" references what team they are on that month. If it is blank, they aren't with the company any longer. The formula I am using is intended to import this data elsewhere, and is formatted like this:

=IF(ISBLANK(B2), "", A2) - My understanding is that this checks to see if B2 has data, and if it does, it inputs the employee name (A3) in that cell.

My ultimate goal is to be able to change the column referenced after the "isblank" calculation based on the date. So if today is April 2013, I want it to check B2, but if it's December 2013, I want the formula to check J2. Is there a way to do this? I don't mind if it's two steps (like if I have to put the date somewhere in the spreadsheet in order to run the calculation), but ultimately it would be the type of thing I could do that would leverage the existing data set so that I don't have to maintain two different spreadsheets of information.

I'm trying to create a formula that will search for a specific text in "sheet1" within a date range and then SUM the total on "sheet2". I've manage to use the SUMPRODUCT (below) formula but want to expand on this so the SUM can be shown on another sheet(summary page). =SUMPRODUCT((ISNUMBER(FIND({"nmlclex06"},A1:A10)))*(B1:B10)) In this example I search for the text "nmlclex06" and SUM the size, but don't have a date range and don't know how to SUM the data on another sheet.

Columns: Name, Size, Date

nmlclex06114/12/2013

nmlclap121012/12/2013

nmlclex0651/10/2013

nmlclex0619/10/2013

nmlclex0611/01/2014

nmlclap12124/10/2013

nmlclex06113/11/2013

nmlclap12118/12/2013

nmlclap1219/12/2013

nmlclap12130/12/2013

In one spreadsheet, I want to have a command button that will hide all rows where the date column (column A) shows a date older than one week from today. When this button is clicked again, all rows will unhide again. Preferably the Command button title would change to reflect whether it is on the hide or show cycle (for example "Click to Hide all older than one week" and then "Click to Show all events") .

View 9 Replies View Relatedi want to return a value from a cell in another workbook where part of the the workbook name is determined by a date set on the current open sheet.

so on my open sheet i have a cell where the user can change the date and i want the below formula to then use the date to look at the relevant file.

any ideas would be appreciated. know how to do this in VBA but no clue with formulas.

what i am trying to do is concatenate part of a file name and a cell value which contans a date

=VLOOKUP(A4,[accountsummary17022008.xls]Sheet1!$A:$B,2,FALSE)

Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc.

I added a new cell called Track Back on the employee search sheet.

What I want to do is only show variances for the amount of days back selected in the Track Back cell.

For example if I select the last 30 days, only the last 30 days would show up below in the sheet.

I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me

I'm needing a formula that will determine the number of days that fall in a specific month based on a date range. For example, if I have a date range of 10/15/2009 to 01/13/2009, I need the formula to determine the number of days in each month within the range (October has 15 days in the date range; November has 30, December has 31, and January has 13.) I have a large spreadsheet that would be so much easier to manage with such a formula. Currently, my spreadsheet is setup as follows. I need the forumla automatically fill in the number of days under each month.

Stard Date End Date Oct-09 Nov-09 Jan-10 Feb-10

10/15/2009 01/13/2009

I'm using Excel 2007.

I have a spreadsheet with four columns of text.

In column A, i have multiple levels followed by a letter (i.e. Level 1A, Level 1B etc).

In column B, i have some other details and then so on and so forth.

In column C/E/G lets say, i want to copy the information from column A to show only items that appear as "Level 1" (not "Level 1A", i only want it to check for things without the letter at the end). Then the same in column E but with "Level 2" and so on and so forth.

Column A...Column B-Column C...Column D--Column E...Column F--Column G...Column H

Level 1A....Metals----Level 1A....Metals ---Level 2A....Integral---Level 3A....Television

Level 1B....Energy----Level 1B....Energy--- Level 2B....Flowers---Level 3B....Kitchen

Level 1C....Synergy---Level 1C...Synergy--Level 2C....Full

Level 2A....Integral---Level 1D....Orders

Level 2B....Flowers

Level 1D....Orders

Level 3A....Television

Level 3B....Kitchen

Level 2C....Full

I also have data in Column B that is to do with column A (i.e "Level 1A" - "Metals") and so on with the following columns. I want the items that are in column B to also move over to column D when the things from Column A move to Column C, so at the end it will appear as below so it appears as above.

I am trying to filter my excel sheet based on certain digits in a column of part numbers. The part number has 10 characters. I would like to filter it so that all part numbers where the 4th character is the number 5 or 7 is listed and where the 5th character is a zero. (i.e. R4X5831310 is a part number where 5 is the 4th character; I would like the filter to show this part number)

View 2 Replies View RelatedI'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.

=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})

so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.

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]....

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.

Budget Picture.jpg

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.

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)

What I need to do is that I have electrical tools that get tested on a certain date in Column A. I need Column B to look at column A and determine and display in date format the next quarter when test is due.

Practical example: Extension cord got tested on 04/04/12. The next test needs to be conducted on 01/06/12. If the test is overdue and gets conducted on 02/06/12. The column A will change to 02/06/12 and column B should change to 01/09/12 as a result.

The formula I have got to work is

=DATE(YEAR(F2)+IF(MONTH(F2)>9,1,0),CHOOSE(MONTH(F2),4,4,4,7,7,7,10,10,10,1,1,1,),1)

but this makes the quarter dates as APRIL, JULY AND OCTOBER - they need to be MARCH, JUNE, SEPTEMBER

when I change the formula to

=DATE(YEAR(F2)+IF(MONTH(F2)>9,1,0),CHOOSE(MONTH(F2),3,3,3,6,6,6,9,9,9,1,1,1,),1)

The dates work BUT if the following test is undertaken on 01/03/12 the date in Column B does not change to 01/06/12. Why? How do I get it to work?

I think I am working all around this in Excel 2003 and feel like I am in the neighborhood, but can't seem to quite get there. On sheet1 I have 2 columns of 100s of rows. All of the cells in column A will be filled in with a date as time goes by (1-May-09 for example). Several of them may be 1-May-09 as a matter of fact. In column B, some of the cells will have a number in them and some of them will be empty. On sheet2, I want to construct a formula that returns a count of cells in column B that have a number in them based on a specific date in column A. For example:

AB

1-May

1-May777

1-May

1-May

1-May

2-May999

3-May

2-May

1-May

2-May111

1-May

If I could get the formula right, it would return an answer of 1 for 1-May in the example, because only 1 of the 1-May entries in column A has a number in the adjacent cell in column B. A formula for 2-May would return an answer of 2 since only 2 of the 2-May entries in column A have numbers in the adjacent cell in column B, and so on for the month. If I understand things right, there are too many arguments for COUNTIF. It seems that SUMPRODUCT should work, but I can't make it so.

I have a 2 groups of column headings with a different month and year in each heading so

1st Group of columns range

Columns AJ through AX

Column Heading example "Expense Ratio February 2013......next Column over is "Expense Ratio March 2013"

2nd Group of columns range AY though CE

Column Heading example "Capital Balance February 2013......next Column over is "Capital Balance March 2013"

Each new month I need to add a new Expense Ratio column after the most recent expense ratio Column. (i.e. Find "Expense Ratio March 2013" and I need to add a column after that with heading "Expense Ratio April 2013"

Same thing for Capital Balance - add a new Capital Balance column after the most recent Capital Balance Column. (i.e. Find "Capital Balance March 2013" and I need to add a column after that for "Expense Ratio April 2013"

Because the ranges keep changing month over month, how do i do this.

Col C = Text and Col F = dates

I would like to count the # of times a value occurs in Col C based on a date

range in Col F.

I am trying to Count (or counta?) cells in one column if they have data but only if within a date range in another column.

So, in the example below, I would want to count how many cells in column E have data in them, but only if the date in column A falls between Jan 1 2014 and Jan 5 2014. (In this case, result should be 4).

A

B

C

D

E

01/01/2014

[Code].....

I am trying to create an array formula which confirms whether a specific date falls within a list of date ranges. I have a list of start dates in column A and end dates in column B. Each row represents a date range. How can I write an array formula to check if a date falls within any of these data sets (i.e. date ranges)? I have attached a sample spreadsheet to show

View 7 Replies View Related
Copyrights 2005-15 www.BigResource.com, All rights reserved