I need to find a formula which will give me a total of £'s between a start and end date.
MY data is an extract with names, individual dates (Ie, 01/05/08, 02/05/08, etc) and costs per day. What i want to do is show a total for the month using the start and end date of that month to add up all the costs within.
I have start dates (column A) and maintenance expiry dates (column B). What I want to do is set up a formula to see if the product was renewed between 01/04/2007 - 31/03/2008 and return a simple yes, no or new_sales.
Examples
If the start date was 26/09/2006 and maintenance expiry date was 26/09/2008 then it would return a yes that they have renewed
If the start date was 18/04/2006 and maintenance expiry date was 18/04/2007 then it would return a no that they have not renewed
If the start date was 20/06/2008 and maintenance expiry date was 20/06/2009 then it would return a new_sale
I have the following data:
Column A = Date
Column B = Reservations made per day
For ex:
A B
1 3/1/2011 5
2 4/5/2011 10
3 3/8/2011 15
Then I have a look up table where based on the date ranges it assigns a week number.
WeekDATE Range 1Date Range 2
718-Feb-1124-Feb-11
825-Feb-1103-Mar-11
904-Mar-1110-Mar-11
1011-Mar-1117-Mar-11
1118-Mar-1124-Mar-11
1225-Mar-1131-Mar-11
1301-Apr-1107-Apr-11
1408-Apr-1114-Apr-11
1515-Apr-1121-Apr-11
1622-Apr-1128-Apr-11
I am looking for a fomula that would assign a week to the corresponding dates on column A and tha would then add all of the reservations booked for each week.
I would like to get the total in column B but its not giving me the correct total. So I would half to use a helper column in column D to retrieve correct results. Is there anyway I could have an all in one formula for this. I would prefer not to use the helper column. The correct result is in cell D23.
View 9 Replies View RelatedExcel Forum.jpg
So I want the SUM of the total from a certain range of dates to go into the Week Columns on the right. I'm gonna manually select the date ranges for each Week but how can I set up a formula to Sum the Totals at the right of the table based on the Date Column.
I am trying to calculate a total of a supplier between 2 dates. The total must fall in between the entered dates but the dates can be any given date and not limited to the dates in the supplied list. See attached scaled down example?
View 2 Replies View RelatedI 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.
see the attached workbook with two different sheets (same data) using different formulas. Each has problems (red text) preventing me from moving forward.
I need to calculate time in level 1, time in level 2 and total time for each row and then be able to sum for the month. Unfortunately, sometimes there is missing data, but I still need to calculate everything possible using a consistent formula that can be applied to the column/row universally.
I have a spreadsheet that tracks “points” for hourly associates on a daily basis that totals weekly and monthly. This spreadsheet works on a rolling calendar year instead of a fiscal and I need to be able to capture the totals for periods of time by days.
For example, I would need to view how many points ‘employee x’ has from 2/25/09 through 2/25/10. I have attached the spreadsheet, which includes tabs for each week ending and a summary page.
I need to create a running monthly total formula for a worksheet. This is something i need in the data, so i can manipulate it without using a pivot table.
I think I would use the SUM, MONTH, and IF formulas, and maybe EOMONTH.
I guess where I'm stuck is figuring out how to make conditions for my SUM formula. So as the dates go down the page, I would like a running total in a column to the right, that will also restart with every new month.
Attached is my example.
I have two (very large) data tables, one with unit costing, and another with counts, similar to the following:
Table - Unit Costing
........A......B........C........D
1..ITEM..TYPE1..TYPE2..TYPE3
2..widget..$5.....$10.......$20
3..gadget..$2.....$3.........$4
4..thingy...$3.....$5.........$7
5..dobop...$8.....$9........$10
Table - Counts:
.........A.......B........C.........D
9.....ITEM..PROD1..PROD2..PROD3
10..widget....0.........1.........3
11..thingy.....3........5.........1
12..dobop.....2.........0.........8
and this is what i'm looking to do with it...
result table:
..........A.......B........C........D
...............TYPE1..TYPE2..TYPE3
15..PROD1
16..PROD2
17..PROD3
so in cell B15 I put:
{=sum(vlookup(A10:A12,A1:D5,2,FALSE)*B10:B12)}
I'm expecting $25, but I'm getting 0
It looks to me like vlookup (specifically the lookup value array) can't be used like i'm trying to use it here...
I've tried the formula:
[Code] .....
But it doesn't seem to be working?
I have cells that have added up totals on each sheet and I would like to have a "total of the totals" if that's at all possible?
It just becomes confusing when there's 15+ worksheets!!!!
I am trying to write a formula for my account statement its got a list of dates of invoices descriptions then the value of invoice.
I want to check the dates of invoice to the date of statement if less the 1 day invoice total to go in a box called current, if between 2 and 30 days to invoice value to go into 1 to 30 days past due, if between 31 and 60 days invoice values to go into a box called 31 to 60 days past due, and then if dates are between 61 days or more then invoice totals go in to a over 61 days over due box.
I have to calculate the totals for every individual for the past three months. I have to do this for 200 people, is there a formula i could use to do this? Eg. if a person made 50 sales in Jan, 40 sales in Feb and 100 sales in March i need a formula that calculates the grand total, which will be 190.
View 3 Replies View RelatedJanuary 31, 1900March 2, 1900April 2, 1900259555357March 2, 1900
View 2 Replies View RelatedI need is a formula that averages totals from different months out of the year. I already have a yearly average. That was easy. But what I need is a 3 & 6 month average.
I also need it to be most current, so when I am in September, it will take the 3 previous months and average them and same with the 6 month. Then when I move into October, it would take its 3 previous months, i.e. - July,August, September.
I have two columns with total hours worked at different places and a grand total for both together in a third column.
I'm trying to sum the total hours for the week so far from Thursday till the following Wednesday for each week.
I want the wookbook to look up todays date and tell me what the total hours are for the current week
I'm using Excel 2003 - see the file attached
I have an issue whereby I have thousands of lines of data. I need to bring back the "previous month" worth of data (along with some additional information) but not sure how to go about this.
The attached shows the example file. I need the following :
1. Take the data to populate the "Populate" tab
2. Using Column F (Invoice Date Created) to only bring back the "previous month data" - in this case it would be May 2014
3. The following formula is what I have currently for Column B of the "Populate" tab: =SUMPRODUCT(SUMIFS('Data'!$N:$N, 'Data'!$M:$M, "Invoice Payment Processed", 'Data'!$A:$A, A2))
4. The above formula brings back all the values - I only want the previous month.
5. I am then not sure how to calculate Column C - which is a count of the amount of invoices that make up the total
I am trying to get the formulas to work on Sheet 3, the rows formated to percentages correctly, but they are not reading the totals in row 2 to make the formula complete. I also want this to run more than one number, I would prefer a range for example value >= 1.29 so that Sheet 3 shows all items less than 1.29.
View 4 Replies View RelatedI have been looking at a post number 170404 which NBVC answered with an example grid. This was while I was trying to bring searched data through to another sheet and this is just what I was looking for as a brilliant start,
I have attached my very similar example in my scenario. I have also got NBVC's example on the first two sheets of this as reference. In addition I am also bringing through from-to date ranges which I then need to be applied to a Booking Sheet so I can see that these dates are booked for this person, and ideally where they are going and what they need to do there by colour.
I tried, just as a test in this example the following. Please note that this only had the three ranges as I was testing an example coordinator which returned three date ranges. It could be that the example returns 20 or 30 ranges that need to be applied to the rather primative Booking Sheet.
=IF(OR(AND(C10>='Coordinators list'!F5,'Coordinators list'!L5<=C10),(AND(C10>='Coordinators list'!F6,'Coordinators list'!L6<=C10)),(AND(C10>='Coordinators list'!F7,'Coordinators list'!L7<=C10))),"yes","no")
It seemed to give the right answer initially but trying different dates it gave the wrong result. It seemed to be checking one of the ranges and not all of them. As mentioned above this was just an example to see what I could get working but I don't think that this would be the ideal way of doing this as it would need to check 30-40 maybe more ranges depending on what comes back from the coordinator search.
The aim would be to choose a co-ordinator on the Site Planner sheet and ideally return the city rather than the yes/no in the Booking Sheet for that date and use conditional formatting so that if the type from the Site Planner Sheet was paint it would fill in one colour, clean in another, etc.
Was hopefully trying to get this done without using any VB as I am trying to resolve this for my Wife she isn't very tech savvy and and I need to be able to explain the formulas!
I'm sure you will see from my sheet I have given this a good go but I am a bit unsure on what some of the formulas on NBVC sheet are doing, and that this is my first attempt at doing anything like this. I have also tried using lookup and match but I'm still having issues that they either aren't always in date order and it seems to return no if it doesn't match the first range. The other main issue that I have seen is that I need to check whatever ranges the selection comes back with rather than adding each one to search through.
I hope this becomes a bit clearer on the sheet I have attached,
I have a table with three columns. I'm building a calendar on a separate worksheet and am looking up the "value" based on a calendar date. So if a date falls within any of the ranges, I'd like to return the value in column C. For example, if the date is 02/07/12, I'd like for the result to be value 1, or if the date is 04/17/12, then I would like the result to be value 3. I've used a nested vlookup, but all that give me is the value when either the start or end dates match, but I can't get a value when the date falls within the range. If the dates were consecutive, I would simply use vlookup/TRUE, but the dates are not consecutive.
ABC102/06/1202/09/12value 1203/12/1203/15/12value 2304/16/1204/19/12value 3405/21/1205/24/12value 4506/25/1206/28/12value 5606/25/1206/28/12value 6
table1.jpg
I am looking for a formula that will satisfy the following:
1) find all the values in column "A" that match
2) In column "G", sum up all the values in "F" that go with the matching values in column "A"
3) For example, in rows 14-16, the values in column "A" match. Cell G16 sums up F14:F16
I need an IF formula, that states if a date is more than a month after the date in the cell then it is timely and if it isn't then it is not timely. For example the date in A1 is 12/11/05, if the cell is A2 is 13/12/05 (or any date after that) then A3 should read Not Timely, if A2 is 12/12/05 (or any date before that, including a date before the date in A1) then A3 should read Timely. Is there any way to do this?? At the moment I am having to go through manually and put in either Timely or Not Timely.
View 2 Replies View RelatedI have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year
Column A is for date input and the same date can be repeated several tumes :-
1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09
Sometimes there are all 30 /31 days but normally not .
I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.
I want to have a new macro/button to redo the chart cells based on the dates in cols T and U (abt 400 entries). T has the start date and U has the end date.
Row 9 - X9: CJ9 have dates for about 10 months (weekdays only)
Column J have either of these two values: A or B
Column C has either of these two values: C or blank
I would envisage it going through each row, checking:
If date at top of that col was between the dates in cols T & U
then
if col J = 'A' then set cell to black block
else if col J = 'B'
then
if col C = C (closed) then set cell to red block
else set cell to blue block
[URL]
take a look at the attached example and let me know how to make the formula work?
For each "title" in column A, I want to compare the two sets of start and end dates (columns B & C versus D & E) and if the date range in column B &C overlaps with the date range in column D & E, input "yes" or "no" in column F.
In my attached example, rows 8,9,11, and 12 would be populated with a "yes", the rest would be "no".
Sum based on date ranges. I am creating one logical formula.
I have an excel sheet with two columns.
A1: Date B1: Time Spent
Date
Time spent (in Minutes)
Total
[Code].....
As per above figure I want to sum based on the same dates. I.e A2 has date 1/26/2012 for which first entry is 12 so total will say 12 in C1, for the same date second entry is 14 so 12+14=26 is there in total at C2, for the third and last entry of 1/26/2012 it's 16 so total would be 12+14+16 = 42 in C3 which is grand total for 1/26/2012. I want to fill entire C column with some logical formula so that the logic holds true for various dates.
I have tried to use the IF formula like =IF(A1=A2,SUM(B1:B2)) but it fails at third cell. I have tried several ways to do this but no luck so far ....
I have one worksheet with 52 rows for each week of the year with the
following headings: week #, start date of week and end date of week. I have
another worksheet where I need to forecast expected expenses for new
employees based on the date they start ie. Joe starts in Feb 6 (week 6) and
then calculate expected spend based on remaining weeks in the year. I do
this manually right now. How can I automate this?
There is a database with work plans for a project cycle, which contains employee names, types of activities, planned working hours for each activity (not in time format, just number) and dates when these hours will take place. Each employee may have one or multiple activities and each activity has different number of hours planned to complete and different dates. My aim is to calculate the number of hours planned for each employee (to sum all activities) within a certain period of time.
So, what is the question? I need to calculate the number of workdays within the intersection of the planned period for the activity and my given period of time.
I'll give a small example to illustrate what I mean.
I'm calculating planned working hours for this week, which is 11/23/2008-11/29/2008. One of our employees, let's call him John, is going to work on three activities, A, B, and C. We have the following plans:
Activity A - 16 hours - 11/24/2008-11/27/2008
Activity B - 45 hours - 11/15/2008-11/25/2008
Activity C - 10 hours - 11/26/2008-12/01/2008
As you can see, activity A is going to be complete within this week, it's perfectly fine. Activities B and C should be counted separately, because they include days earlier or later than this week. Is there any way I can easily geet the number of workdays that are included in the plan period and this week?
Another thing to mention is that I'm working in Excel 2003 and it's not acceptable for me to install the Tools Pack, because I need this firmula to work on other employees computers and there is no chance they all will install it too.
I have about 20 files with check posting dates stretched over several years. Maybe a million checks total. The company uses 13 four week periods each year, not a calendar month/year. I need to look up each posting date and attach the correct period to it.
I could do a VLOOKUP with one row for each day of the year, but that would require 2 x 365 row's for just two years. There must be a more elegant way, maybe using an array or some IF statement.
Attached is a sample file, xlsx, with the perior/year info and sample posting date info. Can someone point me in the right direction so that I end up with the correct period/year in column 'C' for the applicable posting date?