SumIF With Dates: SUM All Expenses After The Posted Date Including That Date
Feb 13, 2009
In the expense log, Column C is a list of Dates and Column I is a list of expenses. I want to Sum the expenses in the 'Expense Log 09' to a new sheet based on a Date entered in H24 on the new sheet. I have tried the formula as shown below and Get the result #NAME?
=SUM(IF(Expense Log 'Expenses Log 09'!C8:C100,H24,'Expenses Log 09'!I8:I100)). I would Like to SUM all expenses After the posted date including that date.
View 5 Replies
ADVERTISEMENT
Jun 6, 2008
I am trying to capture billable and non-billable expenses in a time sheet by date. I thought it was pretty simple, but after a few days of battling with the syntax (I am pretty new to macros)
I have attached the file I am referring to and highlighted in yellow the significant cells.
1. Trying to get data from the all expense sheets to the "Time Sheet" Tab by date and category.
2. Need to show the billable expenses expanded out to each category (meals, hotel, etc.) by date.
3. All nonbillable is summed up in one column by date.
June expense report has:
Date of ReceiptExpense Type "Billable? Total USD
01-Jun-08 Airfare Yes - Recoverable$2,000.00
01-Jun-08 Airfare Yes - Recoverable$2,000.00
01-Jun-08 Ground Transport No - Training$20.00
05-Jun-08 Meals & Entertain Yes - Recoverable$15.00
05-Jun-08 Meals & Entertain Yes - Recoverable $50.00
1) There are TWO June 1, 2008 items that are "Airfare" & "Billable", therefore in the "Time Sheet Tab, I need it to show that under "Billable Airfare" for June 1, 2008 that it is $4,000.
2) Same as June 5, 2008 for Meals & Entertain.
3) All Non-billable (No - Training; No - XXXX; anything with NO) are summed up in one column by date in the "Time Sheet Tab"
4) There are multiple Expense reports and I need the macro to run through all of them dynamically as they input the data in to show on the "Time Sheet" tab.
This would make my job a lot easier if I could get this running. I dont' think it is too complex, but obviously too complex for me. I started on some of the vba below. I do not have all the parts yet, weird thing is, it did spit out a number, but now it is not. I'm at a loss.
...................................
View 9 Replies
View Related
Mar 10, 2007
I'm a Marine Officer in Iraq and one of my reports requires me to determine how many days have elapsed since the last time one of my helicopters has flown. We utilize Julian Dates to refer to aircraft fly dates and one of my reporting requirements is to note any aircraft that haven't flown in 30 or more days. Essentially, I want to count how many aircraft haven't flown within the last 30 days. Cell D4 has my current Julian Date (let's say 07070). I have a cell range of D7 through D22 that lists all of the last days that my aircraft have flown. I would like to have a formula that counts the long term down aircraft. I tried something similar to this:
=countif(sum(d4-(d7:d22))>29)
View 11 Replies
View Related
Apr 2, 2009
I have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.
For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.
View 9 Replies
View Related
Feb 18, 2014
How to filter the pivot table in the attachment by the Posted date. I only want to look at the data for dates within 30 days of today, and can't figure out how to do that manually, much less programatically. The attachement is a small sample of a huge report I receive; all I get is the pivot, which contains tons of information. I filter it down by my various criteria, but that's still hundreds of thousands of lines. Limiting to the last 30 days can make a huge difference, but I can't find the trick to it.
Example.xlsx‎
View 14 Replies
View Related
Oct 29, 2009
I have todays date in cell A2. I have a range of dates in a row 4 and numbers below each date in row 6. What i want is to Sum the numbers below the dates in row 6 if the date in row 4 is with in 90 days before or after the date in cell A2.
Example......
The answer i should get is 4 since only 3 dates fall 90 days before or after todays date.
View 3 Replies
View Related
Oct 31, 2008
This is a project plan with tasks and dates. Column A is the activity number. (Example 1, 2, 3" etc). Column B is the task (Ex. "Complete Report"). Column C is number of days required to complete the task. Column D is the dependency column. (Ex. Cell D2 =1 in other words Task 2 is dependent on task 1). Column E is the date.
I would like to have a seperate start date cell and a go live date cell.
The objective is to enter a start date, and have each column E date increase based on the number of days entered in Column C. If a task is dependent on another and I change the number of days in Column C I need the dependent task to change the same amount of days.
View 9 Replies
View Related
Mar 22, 2007
I’m trying to save a new workbook with the current date included at the end of the filename, in the format yyyy-mm-dd.
I took a guess at the following but it doesn’t work - the filename comes out as 'Report39163' Can anyone help please?
ActiveWorkbook.SaveAs Filename:="\UsersDataTemp" & "Report" & Format(Date, yyyy-mm-dd) & ".xls")
View 9 Replies
View Related
Apr 30, 2008
How to add 2 "working days" to a date, including Saturdays as work days, and excluding holidays and Sundays? Similar to: = WORKDAY ("01/02/2008", 2, 39451) But instead of resulting in Mon Jan 7th, the answer should be Sat Jan 5th. (39451 is Fri Jan 4th.)
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
May 30, 2013
I'm using Excel 2010. I need to populate a daily calendar with the number of nights spent, extracted from the Date of Arrival and Date of Departure of individuals.
View 2 Replies
View Related
Dec 2, 2013
I know need to modify the formula to include validating if the indexed reference occurs within a certain month.
{=INDEX('Duration Calculations'!B$2:B$5000,SMALL(IF(MONTH('Duration Calculations'!$J$2:$J$5000=10),IF(ISNUMBER(SEARCH("br",'Duration Calculations'!$A$2:$A$5000)),ROW('Duration Calculations'!$J$2:$J$5000)-ROW('Duration Calculations'!$J$2)+1)),ROWS(P$100:P101)))}
Essentially, what I am trying to do is index the value from B column if it meets the following criteria
1) Date in column J is equal to any date that occurs in October
2) the Column A has a text value equal to "br"
If both true then I need to index the value in column B.
The formula as states works finding any value equal to "br" but seems to be ignoring the date range.
View 1 Replies
View Related
Oct 23, 2009
My setup is - excel 2003 sp3 / windows xp
On one sheet (Data) I have a list of action items, each with owner; target date; classification and in some cases revised target date. I'm trying to report on these fields and provide a status, by owner and classification, of how many are overdue; due this month; due next month; due beyond 2mths.
Using a SUM array formula on another sheet I can count the number that are overdue based on date; owner; classification; and target date, but can't find a solution if there is a revised target date. Any guidance you can give would be greatly received.
=SUM((Data!$L$3:$L$27=D$18)*(Data!$P$3:$P$27=$C23)*(Data!$N$3:$N$27
View 9 Replies
View Related
Mar 16, 2004
Is there any way of creating a log that logs the last user that saved the workbook including the date and time. I would like the log to be a sheet hidden in the workbook. Not many people will be saving the workbook.
View 9 Replies
View Related
Jan 30, 2009
How do I modify my formula (below) to include an OR statement?
=SUMIF(I4:I20,"Core",G4:G20)
I need something like this:
=SUMIF(I4:I20,"Core" or "Existing",G4:G20)
View 9 Replies
View Related
May 3, 2009
look at the attached file - it was a CSV file. i want to convert the column of dates to say Mar 14 2009 type date. but it only converts some of them.
note some are on the left and some on the right.
View 5 Replies
View Related
Nov 24, 2008
I have faced with a problem with Excel when I wanted to find the average for a stock price.
I have 2 columns:
1st Column: Dates from 1st May 2007 till 23th December 2008 (Rows:A3:A403)
2nd Column: Stock prices from 1st May 2007 till 23th December 2008 (Rows:B3:B403)
View 9 Replies
View Related
Jan 18, 2010
How can I reflect the correct trainee count for Day1-Batch1?
Currently columns F,G,H computes for expected, actual and total variances of trainee respectively reference to the training date J3.
Since this is a 2-day per batch even, I could not capture the 1st day trainee total of every batch because I divide the sum by 2 (2 day / training batch). The sum is only corrected on the 2nd day.
Attached herein is the sample file.
View 9 Replies
View Related
Mar 25, 2011
I have this formula:
=SUMIF(B1,">=C3:C5000",F3:F5000)
Cell B1 has a date
Every cell in column C has a combination of dates
Every cell in column F has a balance
I want to add those balances only if they are less than or equal to cell B1... w/my formula I get zero.
View 7 Replies
View Related
Jan 22, 2013
I have written the formula below:
=SUMIF($A$10:$A$77,"
View 5 Replies
View Related
Jul 2, 2007
Just getting into Excel and lookups want to SUM a 'Profit' colum if the date colum is less than TODAY - I basically have a set of data - 1 sheet per year where the dates are displayed dd/mm I want to achieve the SUM of the Profit Colum if the date is less than or equal to todays date (dd/mm only) for the year so we can compare year on year.
View 9 Replies
View Related
Dec 13, 2006
I just became a member of your forum, so please forgive me if I am not concise enough or take too long.
I imput the number of hours associated with week ending dates.
A C D
Date Hours OT
01/08/2006 6 2
03/12/2006 8
04/16/2006 3 1
I need to add up the hours for each quarter for Hours & OT
Jan-Mar
Apr-June etc
This is what I came up with, but it doesn't give me the correct value
= SUMIF(A3:A75,"<="&DATE(2006,03,31),C:C)
View 9 Replies
View Related
Apr 9, 2013
I have the following formula and it works.
=SUM(IF(AND((Transfers!B6="Labour"),(Transfers!C6="In")),Transfers!G6,""))
Basically the sheet 'transfers' has a column named type and one named in/out, the current formula I have does a logical test on both of these and then if they are both true takes the cost of it from a column named cost. This works fine for single rows. However on another sheet I am trying to use the above formula but there will sometimes be more than one entry for the same date on the sheet 'transfers' and all the same dates that meet the logical test's conditions need to display the sum of the cost on this other sheet.
View 1 Replies
View Related
Apr 28, 2014
I am trying to get a SUMIF formula to work where the following: - RANGE = Column N:N (These are all a list of dates) CRITERIA = Cell C20:D20 (These are the dates i want to look at) SUM_RANGE = Column P:P
Basically, I want to SUM all the numbers on Column P where the date in Column N falls between and including the dates in Cell C20 and D20
In attachement, I want to show in the YELLOW cells the SUM of Column P where the Date in Column N is between the Dates in column C and D.
View 3 Replies
View Related
Jul 23, 2014
I am trying to find a formula for column D on the PO Costs EOY sheet. My criteria is as follows:
1. Column b on PO Costs EOY= Column A on Fabric and accessories
2. The order date in Column A is equal or in between the dates on rows 3 and 4 in fabric or basket accessories
3. I do not want to include in the some anything before order date, ie, if the order date is 8/8, i only want to sum 8/9 and greater.
The order date will always be on the last day of the week. So the sum would start the follow week.
View 3 Replies
View Related
Jan 8, 2014
I am working on a report for work with the following formula:
=SUMIF(A5:G8,AND(between 1-1-13 and 1-31-13,"soft cost"),G5:G8)
my formula is "IF A5:G8 IS BETWEEN JANUARY 1st 2013 AND JANUARY 31st 2013 AND ALSO IF IT IS A SOFT COST THEN GIVE ME THE SUM OF THAT ROW"
Pretty much column A contains different dates and column C indicates whether a cost is a "hard cost" or a "soft cost". If the date is within the month of January AND if it is a soft cost, I need the dollar amount in column G summed-up (must meet both criteria - January and soft cost).
View 4 Replies
View Related
Jan 27, 2008
Im Trying to use a formula of countif or sumproduct
so the formula will read the range of cells from A2:A30 to see if the word Hydro is in any of those cells, then if the date range from Cell B2:B30 is from range =>Dec-01-2007 to =<Dec-31-2007, then it will count 1.
[Code] ........
But this formula doest count.
I've tried using a multiple if count if with arrays and sumproduct. I really want it to count the ammount of time the work hydro is used during the month on december .
View 3 Replies
View Related
Dec 22, 2013
I am trying to use a date range as a criteria in a SUMIF function. Below are the data and formula I am using.
Forecast
6
4
15
8
Week
1/12/2014
1/19/2014
1/26/2014
2/2/2014
Formula =SUMIF(G2:J2,">L1",G1:J1)
G1:J2 - raw data shown above. L1 = 2/2/2014. Desired result = 8. Result obtained with formula above = 0 how to make this work?
View 3 Replies
View Related
Sep 11, 2009
I am trying to sum multiple columns of data by a sumif based on a criteria in column C, the columns are weekly dated, and I wish to match columns by offset this initial column with a start date and end date and sum columns in between, I have been getting close but only returns one columns values?
SUMIF(FilterCol,$C17&I$9,(StartCol:EndCol))
FilterCol is column for criteria match
Start Col is name range - OFFSET(FilterCol,0,'Summary totals line groups'!$G$4,1,1) - End col similar, (G4 is a match date to find column ref)
View 9 Replies
View Related
Mar 24, 2014
I have a column of hours spent working on a particular task, but I only want to add the hours that fall within a two week reporting period.
Is it correct to use the SUMIF formula? If so how do I write the formula to include the date range desired? Would it go under 'criteria'?
View 2 Replies
View Related
