I have to calculate the flat revenue split by month between 2 different dates. The two dates may be in the same year or different years.
excel formula for the same.
Is it possible to do the same in pivot?
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
I need to calcuate how many times a date( any date for the month) aprears in the list. it needs to be calucated by each indivual month form a list that will span over a 12month period.
the problem i'm having is how do i get it to only read the month that i want?
I trying to find a formula to give the difference between two dates in year and month.
For example, the start date is Feb 1 2006 and end date is Jan 1 2008.
The formula should result in 2.11
I tried the following formula from a previous thread but it gives the answer in text but I need it to result in 2.11.
[url]
I have created a model that shows me the money that I am owed each month. However, I receive the money over a 3 month period.
Using a formula I need to determine how much I actually receive in total each month, factoring in that I receive each months revenue in three equal payments over three months.
For example
Month 1 Month 2 Month 3 Month 4 Month 5 Month 6
$A $B $C $D $E $F
In month 1, I am owed $A, but this payment will be split over 3 months.
So in month 1 I collect $A/3. In month 2, I am owed $B but this payment will also be split over 3 months so in month 2 I collect ($A/3 + $B/3), etc etc. In month 3 I collect the ($C/3) + ($B/3) + (last payment of $a/3) etc etc
How do I present this in a formula so I dont have to adjust every single column manually?
I need to follow the same structre for another scenario using 18 months as well.
I have sales data for Clients that has the client name month and year(combined for date) and revenue for each month. I would like to take all clients in a given month and see the average revenue per client and then be able to display that in a bar chart by month. I also have the data loaded into a PowerPivot Data Model.
View 1 Replies View Relatedi wish to do some conditional formatting. my target for the first 6 months from date activated is 50,000 after 6 months is 100,000. and for those below target, i will need it to be highlighted in red. thus i have 2 sets of conditions.(calculate the no. of months and the revenue).
View 2 Replies View Relatedis there a formula i can use to sum the sales revinue column of the attached per month into the yellow highlighted cell @ the bottom?
View 2 Replies View RelatedI have created a time sheet in excel (see attached) that will be part of the larger workbook that will be linked with other sheets to auto fill in most fields. I am wondering if there is a way for an user to enter a Month and a Year at the top of the page and that in turn automatically fills in the days of the month by week.
So in attached sheet there are 5 boxes representing 5 weeks in a month. So if we used May 2014 as an example I would like to know if there is a way that once May 2014 is entered in up to top that. Excel fills in the dates in Week #1 with under Thursday showing 1st, under Friday showing 2nd as on for the entire month...
So as the month go by all user has to do is state the month and year and excel fills in the weekly dates for each day in month.
Attached File : Time and Attendance.xlsx‎
In a sheet I enter the following:
... in A1 a year (say 2012)
... in A2 a month, formatting as "MMM" (JAN, FEB, MAR etc.)
How to automatically get in column A (say from A3) all the dates of the month entered, formatting as "D/M/YYYY" (e.g. 1/1/2012, 2/1/2012/ 3/1/2012, etc.)?
I have a range of dates from 2003 to 2012. I formatted them to the 'Mar-01' option, but when I want to pivot on the month, Excel still reads them as the date - example 3/25/2008, 3/28/2008...and so my pivot table has multiple columns for all of the dates present in that month.
How do I truly format my dates so that excel reads them as the month only so that I can then pivot and show 12 columns (months) per year?
I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.
Pivot Table  ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012Â 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26
I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.
View 6 Replies View RelatedI am trying to create vba macro that allows me to calculate cost per month.
I have created a basic table with two columns
Cost
Month
200
Jan
300
March
150
Jan
470
April
150
March
I have another sheet , that has two columns
Month - Cost
Jan
Feb
March
April
I am trying to sum up the value on sheet1 and input into sheet2 on the allocated month.
So far logic , that I have came up with is.
I create a For loop statement that looks column and sort each month into an array.
Than the second loop will use the array to find the cost with the allocated array value to sum it.
In cell A1 I have a date 1/1/14
I need to get the number of business days for the month that is entered in the cell. I tried NETWORKDAY formula but needs a start and end date.
I am looking for a formula that counts from each particular month
The main List has the following
Column C has all dates
Column D has hours worked
Column E has engineers name
The Second List is broke down into months and hours
Column I Has month ie I4=Jan 14, I5 = Feb 14 etc
Column J I need to have a total taken from column D from the month in column I
I've managed to sort the first half of this issue, in that I can calculate the number of days within a given month which fall inside specified to and from date parameters..
The formula I've used is thus :
Code:
=IF(EOMONTH(A13,0)EOMONTH($I$9,0),0,IF(EOMONTH(A13,0)>$I$9,$I$9-(EOMONTH($I$9,-1)),EOMONTH(A13,0)-(EOMONTH(A13,-1))))))
This examines a date value (A13 in this case) and assess whether any days within that month are between the two date parameters specified in H9 and I9.
H9 = 01/01/2011
I9 = 15/05/2011
Jan-11
31
[Code]....
What I need to do now is specify a second set of date parameters in H10 and I10 and add those into the assessment. The dates will NOT overlap.
So, for example, if I were to set H10 as 01/07/2011 and I10 as 15/07/2011, the results would appear as below
Jan-11
31
Feb-11
28
[Code].....
I have a sheet with 3 columns. First one is a date in the format dd/mm/yy, second is category type (numerical 1-40) and then the final column is cost in the format 0.00. These columns will need to run from A2:A65536, B2:B65536 & C2:C65536 to cover all later additions. I need to work out a cost total for each of the categories in each month.
View 5 Replies View RelatedI need to get a general formula which if possible, automatically calculates the frequency of unique texts (no duplicates) under a particular month (January) without manually inserting the cells which is under january. If possible, the formula will also check to see if the company is either Cash or Cheque.
I have three columns; Date, Company name and Payment mode.
Date ranges from 1 jan 2010 to 31 dec 2012. Cell A2 to A234
At the end of each month, my agency collects data about clients served in our programs. One of the many pieces of information they want is the number of "bed days" a client was residing within our programs for the given month.
For example:
Jon Doe entered the program on 7/16/13 and discharged on 8/15/13. I would like to know how to calculate the number of days in July that Mr. Doe was residing in the program. (The date of discharge is not included.)
I recently took over the responsibility of completing reports such as this one at my office. The previous person who completed the monthly reports would count the days by hand for each client, but I know there must be a better way.
I need to calculate an SLA.
I have 1 column called 'Date/Time Requested' containing the time and the date together, as in:
dd/mm/yy hh:00
I also have a second column called 'Date/Time Completed' containing the date and time in the same format as above.
I need a macro that compares the two and works out the following:
< 48 hours = "Within SLA"
> 48 hours = "Outside SLA"
to know the number of hours between A5 and J5 of my spreadsheet - working day is 8.30am to 5.30pm also need excluding weekends and bank holidays in the uk. I need the result to appeer in K5. the date and time format i have is: 2009-02-04 - 11:56:39 in column a5 and 2009-02-16 - 14:23:45 in column J5
I have over 100 of these calculations to do per month and it takes me ages manually.
I have two columns. The first column contains cells with multiple date values in text format (YYYY/MM/DD) in each cell. The other I was planning to be Calculated cells that I thought would compare the string of text (possibly converted to numbers) and determine which is the next in sequence after today's date. As there may be numbers in the past I cannot just use the first item.
The cell of text looks like this:2009-03-27 00:00:00
2009-04-10 00:00:00
2009-04-29 00:00:00
2009-05-13 00:00:00
2009-05-27 00:00:00
The calculated cell (based on today's date) should output:2009-04-10 00:00:00
Problem: I've been unable to figure out a calculation that will accomplish this. The idea I had was to examine the text in pieces and compare them numerically to "today" (as a number instead of a date).
I'm not very adept with Excel functions yet. I've started with this: "=(MID(A2,2,10))" but all it does is return the first item. I also tried using a Replace function to get rid of the dashes and also one that would return the dates as a decimal. But while I can get the numbers to change format, I don't know how to do the compare...
I am starting a project that will involve finding how old people are using the difference of two years. Cell A1 will contain the function that automatically obtains the current year from the PC clock (or a manual input). Cells B1...B6 will contain text (names). Cells C1...C6 will contain the month of their birth. Cell D1...D6 will contain the day of their birth. And cells E1...E6 will contain the year of their birth. Cells F1...F6 will have the formula to calculate the difference between A1 and its respective cell column E.
The only functions I know are =(TODAY) which does not give me just the year. Also, I do not know how I would be able to do the calculation [e.i., =(TODAY) - 1988].
calculating project completion % based on Project start date and end dates .
View 5 Replies View RelatedI need to make a table for an injury category per shift per week. (Falls per shift per week)
I have attached an example of the spreadsheet. I have a formula in the table now that was calculating just the injury type per week but just need to add the function to read per shift but can't seem to get it to read correctly.
I'm trying to calculate shift working hours without using dates.
The scenario is
Cell A1 = Start Time
Cell A2 = Start Time
Cell A3 = Break Time
Basically I want the output to calculate hours worked between:-
0000 and 0600 as a total in cell A4
0600 and 1800 as a total in cell A5
1800 and 0000 as a total in cell A6
Then if the value of A5 is greater than A3, subtract A3, but if the value of A5 is less than the value of A3, A5 should be zero and the remainder of the value of A3 subtracted from A4 (or A6) depending which has a value.
I need to calculate time taken to fix a piece of equipment.
A1 B1
BREAK TIME FIX TIME
4/22/08 23:00 4/23/08 04:00
Should be 5 hours, but i can't find the formula to make it work.
I recently manage to create a spreadsheet. On the spreadsheet what I am looking to do is once I change the year in cell U1 from 2010 to 2011 to automatically change the days and the date number, and where Sat and Sun preferably to auto-fill in yellow the whole column within the table as you can see in the spreadsheet.if not then just do not display Sat/Sun columns at all..
View 5 Replies View RelatedI have put together the following code (with help from this and another forum):
View 5 Replies View Related
