Manipulate Date Ranges For Monthly And Quarterly Reports
Feb 4, 2009
I am currently trying to manipulate date ranges for monthly and quarterly reports, and am having trouble doing this. I have attached a file with an example of what I would essentially need.
I would need the "Nbr of Projects", "Nbr of Days worked", "Nbr of International Projects" and "Nbr of Local Projects" cells filled in under each respective report, based on the data in the top left.
If a project falls in two months, such as "18.12.2008 to 15.01.2009", this would be treated as 1 project for December and 1 project for January. Also, I would need only "NETWORKDAYS" included in the solution, so 11 days in this example.
View 7 Replies
ADVERTISEMENT
Jan 27, 2014
I'm lookng for a way to add functionality across tabs in a spreadsheet where if you put a date and $ amount in different cells on an expense sheet, it recognizes the date of the amount input and it shows up in another tab with a list of monthly expenses.
View 4 Replies
View Related
Jan 27, 2014
I am trying to convert quarterly data to monthly
The quarterly data columns go: Mar-14, Jun-14 etc
The monthly data columns goes Jan-14, Feb-14 etc
I am trying to use a vlookup and match formula but as you will see in the attached file it is only working for those months that are labelled in both data e.g. March 14, June 14
Is there a formula that will pick up for example that January and February numbers should be drawn from the March 14 quarter, April and May from June quarter etc?
View 2 Replies
View Related
May 13, 2014
I am building a Discounted Cash Flow.
I have monthly cash flows rolling for 10 years, which I need to sum up into quarterly cash flows.
View 1 Replies
View Related
Apr 15, 2014
I need a formula that will provide monthly and quaterly averages from another worksheet, but only include the months that have occurred. I have the formula to obtain both the averages and quarter totals, but it is counting all months w/in the quarterly range before the months have occurred.
View 5 Replies
View Related
Aug 25, 2007
My workbook contains 12 monthly spreadsheets (one for each month of the year). It will also contain an additional spreadsheet, I’ll call “Update”.
At the beginning of each month I will copy and paste new account information into the monthly spreadsheet (for whatever particular month we are currently in). The columns (J-N) will be blank. I will then copy and paste account information into my “Update” spreadsheet (this is done twice a week per month, from 8 to 10 times per month). This spreadsheet will contain data that needs to update my monthly spreadsheet (for whatever particular month we are currently in). The “Update” spreadsheet column B needs to populate columns J-N on my monthly spreadsheet and this will depend on column C of the “Update” spreadsheet.
Sorry to be confusing . . . .
Update Spreadsheet
Column B – Payment Amount (dollar amount)
Column C – Commission Type (Low, Medium, High, Super, Duper)
Monthly Spreadsheet
these columns need to be populated with the dollar amount
Column J – Low
Column K – Medium
Column L – High
Column M – Super
Column N – Duper
I forgot to mention that all data is compared via the account number!
However, to make it even more confusing if I am updating my monthly spreadsheet and during the 8 to 10 times I update it during the month I find that I already have a dollar amount posted for a particular account for a particular commission type (Low, Medium, High, etc.) I do not want it to keep adding up the dollar amounts. So, if I have an account that has $25 for “Low” and then next time I update my monthly spreadsheet I have an additional $75 for “Low”, for the same account, I do not want it to calculate it to $100. I need it to either color code that entry on the “Update” spreadsheet or toss it onto another spreadsheet . . . just so it stands out and I know that I have to handle it differently.
View 5 Replies
View Related
Oct 24, 2013
I have a data series running horizontally, which gives me monthly values e.g.
Jan 10 - Feb 12 - Mar 11 etc. Imagine the months are in row 2 beginning from column 2, and the values are in column 3.
I would like a formula which adds the three values, and in row 6 column two gives me the total for the quarter (10 + 12 + 11) and displays 33.
The next quarter value, I would like in row 6 column 3, etc.
View 5 Replies
View Related
Sep 10, 2012
I was wondering if there is an easy way to convert a cashflow present quarterly into a cashflow presented monthly? I would like to divide the quarterly figure by 3 and insert each third into one month on the monthly cashflow.
In Cell H2 I have 01-Apr-12 and in cell H870 I have 30,000. I would like this to go into cells H900, H901 and H902 as 10,000 in each. This process could then be copied along.
View 1 Replies
View Related
May 22, 2014
I'm using a stacked bar chart (in Excel 2010, running on Windows 7) to create a simple Gantt-type chart - with just four or five bars. I've got my chart looking most of the way I want it to, but one thing still eludes me: I'd like to set up the major axis ticks to be quarters of the year (from 1/1/2011 to 4/1/2013). Since quarters are not regular intervals (they are not exactly every 90 days), I can't do this using Excel's standard functionality for choosing axis tick marks.
View 3 Replies
View Related
Feb 5, 2014
I'm trying to figure out a formula that will give me monthly average (per person) on the following report. The problem is that people start at different times and sometimes they don't produce in a given month after they have started. Basically I'm trying to get a monthly average from starting month to the last full month, in this case January.
View 8 Replies
View Related
Feb 17, 2014
I have an expense workbook (Data) with 4 columns (ID, Item, Cost, Date). I would like to create macros that will generate 3 different reports and write to 3 different worksheets.
The first report is sorted and sum up the cost for each item. Please see the worksheet "Item".
The second report is sorted and sum up the cost for each part ID. Please see the worksheet "ID".
The third report is the cost for each month and Year-to-Date cost right next to it. Please see worksheet "Summary".
write the macros for each of these reports assuming that we don't know the number of rows in the "Data" worksheet.
View 4 Replies
View Related
Aug 2, 2013
I have this concept of a table of responses that have to be generated every month or on command. These reports export from a website into a certain format, its easy for me to adjust it so it can populate a the table. Although, i want to incorparate my date picker so everytime i pick a certain date a diffrent set of data will populate from the report. it seems very possible, just need to know where to go from. Either a VBA code or a fomula to allow for this to happen.
Heres and example..(cell linked to date picker)........column1 column2 column3,(date picker from microsoft),,each has a corresponding month
.....................................................................jan
.....................................................................feb
.....................................................................etc.
say i pick january, i want these columns to give me my data from a sheet or other workbook(the january report). i want everything linked so it will do this for virtually every month of the year. ive attached an example, the only thing i can think of is to make the cell that changes with when i pick a date to link to the report criteria.
View 1 Replies
View Related
Mar 6, 2013
I have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What I want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. It's also important to note that it restarts each year.
Link for excel file is here: [URL] ...........
View 1 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
Dec 3, 2006
I have a workbook with 12 sheets for monthly results and a Year to Date sheet. For presentation reasons, the monthly results are listed as seen in the first three worksheets, but I want the YTD results to be structured as seen in the fourth worksheet. I have shown how I am currently achieving this in the "Call" section of the YTD sheet, but as there are 12 months and many more variables in the original workbook, I'm wondering if there is any easier way than the one I'm using.
View 4 Replies
View Related
Aug 3, 2009
I’m currently pulling data into two columns labeled “Monthly” & “Non-Monthly” respectively. They indicate work orders with a frequency of “Monthly” or “Non-Monthly”
The Monthly data is obtained using the following formula:....
View 9 Replies
View Related
Jun 17, 2014
My excel sheet contains a set of date-marked expenditures. It's all one running list - no starting a new column for each month or anything like that.
I'd like to include a separate column/sheet showing total expenditure for each month. I've tried multiple formulae, along the lines of "Sum the B cells but only those for which the corresponding A cell is in the right value range". Or in programming terms, "for i from 1 to infinity, if x < A(i) < y, add B(i) to sum." Since other formulae on the sheet are independent of item ordering (don't require the data to be sorted chronologically), a plus would be if the solution can be, as well.
A separate thing I'd like to do (which I'm guessing has a similar solution) is to include grand (running) totals by expenditure type. A separate column of (non-numerical) expenditure codes is next to the data.
View 5 Replies
View Related
Jan 2, 2010
I have a series of employee variances and dates for the variances in two columns.
I have another section on the same sheet where I want to track the amount of variances & occurances for certain months.
attached is an example of what I am looking to do.
View 2 Replies
View Related
Jan 30, 2013
I'm looking for a function that calculates a fee deduction based on the 28th of each month.
I'm paying back £200 on 28th of every month starting 28th Feb and was hoping that a formula could keep track of this...
A
B
C
D
1
£1,300.00
=TODAY ()
2
-£200.00
28/02/13
FUNCTION
How to do it but basically I'm trying to put a formula in D2 as follows:
If Today's date (C1) equals C2 I need B1 to reduce by the amount in B2
How to continue it calculating reductions per month by duplicating the formula...
View 1 Replies
View Related
Jan 30, 2014
I am trying to create a document that takes our client PO amounts, tactics/spend line items, and dates from one sheet and averages them across the months that it is eligible, so we can know the total amount of money we can expect to go out from our clients each month.
For example:
Client1 Tactic1 StartDate EndDate TotalAmount
Client1 Tactic2 StartDate EndDate TotalAmount
Client1 Tactic3 StartDate EndDate TotalAmount
Client2 Tactic1 StartDate EndDate TotalAmount
Client2 Tactic2 StartDate EndDate TotalAmount
I have mostly figured this out in a really complicated way with many nested if statements, but there HAS to be a simpler way. There will be lots of hands in this document, so I would like to make it as simple and easy as possible.
View 4 Replies
View Related
Jan 30, 2014
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.
View 3 Replies
View Related
Sep 16, 2009
I'm trying to combine monthly calculations with "today" and with "workdays"
Example:
start date = 01/01/2009
today's date 09/16/2009
formula result = 10/01/2009 ; or if 10/01/2009 is a Sunday, result = 09/29/2009 (not 02/01/2009, 03/01/2009, etc)
=edate gives me a month but it doesn't skip weekends or calculate beyond today's date
View 14 Replies
View Related
Aug 27, 2013
I can't seem to figure out to easily aggregate & automate a series of weekly values (ie. forecast volumes by a specific part number) into vertical monthly summaries, by product.
Here's the before picture:
column A column B column C column D column E column F
Part num 1/1/2013 1/8/2013 1/15/2013 1/22/2013 1/29/2013A
100 100 100 100 100B
150 150 150 150 150C
200 200 200 200 200D
250 250 250 250 250
The output after manually doing this would look like this:
column A column B column CPart num Date QuantityA
Jan-2013 500B Jan-2013 750C Jan-2013 1000D Jan-2013 1250
View 5 Replies
View Related
Nov 8, 2012
I 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,
View 9 Replies
View Related
Jan 5, 2012
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
View 4 Replies
View Related
Jul 29, 2009
how do i get excel to automate quarterly calculation? in column A1 I have Year and column B1 i have months (1-12) i have data in column D1 - H124 - I would like to run a macro where it will pick up Jan, Feb, march (1,2,3) for each year in column A and do a quarterly average right now i manually use =(D35*31+D36*28+D37*31)/90 but i would like to avoid human errors can i get excel to read if column B is 1,2, and 3 run quarterly average? and would excel know that in Feb there is only 28days except leap years?
View 9 Replies
View Related
Feb 5, 2014
Each quarter should have the bars standing next to each other, i.e. Q1 2013 should stand right beside Q1 2012. Between these pairs of quarter there should be a small space and the the next pair, Q2 2013 and Q2 2012, should be. So how do I do this?
When I use data without stacking several info in the same bar, this isnt a problem but when I have this "aaa, bbb,ccc,ddd" in every bar, this gets messed up.
View 2 Replies
View Related
Oct 21, 2009
I use Excel 2007 , I created Interest calculator , on Daily basis , to caluculate interest , compounded quarterly.
But I want to make it compact , as d one I created is long enough.
A3 = Principal Amount
B3 = Date of Investment
C3 = Interest as on Date
D3 = Number of Days , amount Invested {comes out of formula set}
E3 = Rate of Interest
Now in F3 I want the Interest amount , compunded quarterly.
Some times NUMBER OF INVESTED CAN BE LESS THAN 90 DAYS TOO...then what ?
View 8 Replies
View Related
Dec 8, 2012
I am very new to excel and my formulas dont add up.
I need to calculate based on my quarterly goal and my quarter to date actual sales what my percentage pacing is.
Now the way i did it is =B5/A5
Do i need to add the Quarter date calculations in there because its not giving me the accurate percentage.
Goal Actual Pacing
View 3 Replies
View Related
Aug 7, 2014
summarize quarterly data in the summary table taking into consideration the months and the district. see the attachment for a more clear picture.
View 4 Replies
View Related
