Macro To Spread Amount Over Multiple Months Using Working Days As Weighting

May 5, 2014

Phasing Design.xlsx

We have a requirement to spread/phase amounts over multiple columns (representing months) using a weighting (represents working days per month).

We will calculate the weighting result in excel first (represented by a % per month), so the macro will be more of an allocation of row amount against the equivalent column %.

So far i have the following code:

VB:
Sub phasing()
Dim SourceA As Range
Dim Weight_ResA As Range
Dim TargA As Range
Dim i As Long

[Code] .....

View 2 Replies


ADVERTISEMENT

Macro- Large Amount Of Data In A Single Spread Sheet

Jul 17, 2008

I have a large amount of data in a single spread sheet. Each row has a branch number on it, there are multiple branch numbers. Each branch number is located in Column A. I want to separate this sheet and put all of the branch numbers into their own sheet. how to do this without manually copying and pasting?

View 9 Replies View Related

Date Function - Calculate Ratio Between Months Based On Working Days?

Jul 25, 2014

I have added 3 tables data .. also I had created a sample solution calc for emp1 and project1 ... I need to calculation the ratio between the months based on the working days and allocate the efforts accordingly.

View 2 Replies View Related

Predict Value Based On Weighting Of Prior Months

Dec 28, 2011

I have 11 months of sales commission data, and need to estimate the value for December. However, the catch is, for the first 7 months, the values are significantly higher than the most recent 4 months. I'm currently using the TREND function to guesstimate the December value, but with the wide fluctuation between the 2 time periods, I'm thinking the result of the TREND value may be way off.

Is there a way to 'weight' the data to reflect the higher values earlier in the year, with the much lower values later in the year?

View 5 Replies View Related

Revenue Spread Over Months

Apr 21, 2009

I am trying to figure how to get a formula to work that will spread revenue number over a four month period. The dates will be dynamic so they will change but the spread stays the same.

I have attached a snapshot of what I am trying to do.

View 9 Replies View Related

Macro For Creating Months - Not Working

May 20, 2014

I have macro for creating months. Code worked fine, but now I use merged cells in range where days should be created, and code no longer works (Error : "Cannot change part of merged cells").

Tried with unmerging cells, but then code inserts days in shorter range that I need, so code needs to be changed.

[Code] .....

Attached File : Macro_months.xls‎

View 14 Replies View Related

Formula To Spread Costs Between Months And Years?

Dec 3, 2013

See attached. (I added in some arrows for the first month so it is easier to follow the logic)

I am working with monthly revenues. Revenues are earned in a specific month but only a % is received in that month and over the next 4 months. I need a formula that will spread these revenues earned in 1 month over the next 4 months.

Currently, I have a complex Index/Match formula which works until September. September, October, November, and December do not work correctly because once the months roll over from month 12 (December) back down to month 1 (January), my formula cannot recognize the range.

Essentially, I need the formula to return the correct amount that is being paid, even across different years.

I don't know if my formula can be modified or a new formula would be more efficient.

(This post originated from Commercial Services, which I just requested be removed from that forum)

View 3 Replies View Related

Spread Revenue Equally Over 6 Or 12 Months Depending On Which Product Is Sold

Jul 15, 2014

We have 5 - six month programs and five - 12 month programs we are going to sell; each have a different price. I Want to insert number of projected sales manually for each month and then have excel distribute/spread revenue evenly over six or twelve months into a separate set of cells. First payment would be received at time of sale.

View 4 Replies View Related

Calculating Working Days For Multiple People

Jan 1, 2009

I'm using Excel 2003, and successfully working out start date (T2 in the formula below) plus duration in days (U2) minus any non-working days listed in a seperate worksheet. =WORKDAY(T2,U2,Holidays!C6:C17)

Each 'task' is on a seperate row, and the lead officer is named in Column E. How can I get the work day function to include the non-working days for each officer as well as the global non-working days in the seperate worksheet? I thought about using a vlookup, but that only matches the first non-working day for each officer rather than all of the non-working days.

View 2 Replies View Related

Downtime Tracking Between Multiple Days Only Counting Working Hours?

Jan 29, 2014

I have a downtime tracking spreadsheet that we want to be able to track the downtime on the line. However, we only want to count the working hours, not overnight, etc. I have added the weekend day work hours and Saturday work hours but haven't been able to figure out how to connect them into a formula with the down time.

View 1 Replies View Related

Converting Days To Years,months,days

Jan 23, 2009

How can I convert the number of days for example 366 days to years, months, days 366=1year, 0 months, 1 day

View 3 Replies View Related

Macro: Return Previous Working Days Date

Dec 1, 2006

how do i get the previous days working date in VBA, how can i get VBA to determine wether we are in the week, and how will i get it to know its a monday to retrieve fridays date?

View 3 Replies View Related

Change Days Added To Working Days

Jul 31, 2014

I've got the below so far, but where it says V2>=(Q2+5) , I would like it to add 5 working days instead, is this possible?

Code:

=IF(AND(T2="Awaiting",V2>=(Q2+5)),"Overdue","Raised within 5 Days")

It would also be great if there is a way to say that if T2="Not Required, then put "Not Required" in W2 (where the IF statement is)

View 4 Replies View Related

Split Equal Amount Across Months Based On Start And End Date?

Feb 6, 2014

I have contarct for amount $5000 with a start date of 1/1/2014 - 3/31/2014 (3 months). I would like to equally split my amount based on my number of months between 1/1/2014 and 3/31/2014 ie $1666.66/per month.

View 4 Replies View Related

Formula To Calculate Percent Change, Varied By Amount Of Months

Jul 24, 2009

I need to figure out a formula for cell F17 that will calculate a percentage change only for the months that have data in 2009. The way it is set up right now I have to go in every month and change the cell reference of the formula to include the latest data. Since the 2008 data is totally populated the formula gets messed up if I include the months of 2009 that have not yet occurred.

View 2 Replies View Related

How To Count Amount Of Days

Sep 1, 2008

Is there a formula to count the amount of days, for example.....a1=8/1/08 b2=8/10/08 then c1=9 days....

View 4 Replies View Related

Amount Of Days In A Cell?

Dec 13, 2012

figure out the amount of days in a cell.

I have dates in "A"

and need the amount of days to show up in "C"

EX:

A B C
11/24/12
12/13/12 18

So i need the code to have the "18" show up when I enter the date in "A"

View 1 Replies View Related

Add X Months Or Days To Date

Jul 29, 2009

I use [=IF(F15="","",EDATE(F15,-D15))] to track time frames. The problem is Edate works great for months but on occasion I need days, like 40 days. In cell D15 I have a list where the user must choose from, but one of the choices needs to be in days so the Edate won’t work anymore.

View 5 Replies View Related

Calculating Date After X Amount Of Days

Apr 11, 2014

I am trying to determine a formula that will tell me what the date will be 61 days and 90 days from a selected date. For example:

A1: 04/11/2014
A2: (Date 61 days from date in A1)
A3: (Date 90 days from date in A1)

Is this possible??

View 6 Replies View Related

Calculate The Average Amount Of Days?

Mar 26, 2014

I need to calculate the average amount of days it is taking for files to be processed. Here is the example I have.

Assuming that B1=0 and C1=1 (in the # of days row)

# of Days 0123456789101112131415Total Files
File Count011712113203000000049

View 8 Replies View Related

Excel 2010 :: Multiple Entries In One Cell That Need To Be Spread Across Multiple Rows

Jun 17, 2014

I need to convert data from column IDS into separate rows, all other columns need to stay in tact. There are several distinct patterns for the IDS column, main identifiers are always starting with FILER or TEAL and the trailing numbers behind it have no more than 6 digits.

BEFORE MACRO

ID
AREA
TYPE
CLASS
QTY
IDS

1
COAL
TYPE9917312
CLASS881345
2
FILER756911**/**FILER123188 ^** FILER877119*118

[Code] ........

AFTER MACRO

ID
AREA
TYPE
CLASS
QTY
IDS

1
COAL
TYPE9917312
CLASS881345
2
FILER756911

[Code] ......

What the MACRO would look like? This is for Excel 2010.

View 3 Replies View Related

Formula To Indicate How Many Days Fall Within Series Of Months

May 23, 2014

I have a sheet that has a start date and an end date for holidays.

The start date for the test holiday is 23/05/14
The end date for the test holiday is 15/7/14

I need a formula that will calculate how many of the holidays (work days only Mon-Fri) fall in each month from May through July.

View 14 Replies View Related

Calculating Years / Months / Days Between Two Dates?

Oct 17, 2009

my spreadsheet is setup with two dates: A date for when someone joins our group A date for when they are released/leave

I want to be able to see exactly how many years/months/days transpired between these two dates. I have tried lots of different formulas with no success.

View 12 Replies View Related

Count Days Of Months Except Holidays Sundays

Aug 8, 2014

I want a formula that counts all days of a month "listed in a column" but excluding sunday.

View 14 Replies View Related

Formula To Calculate Years Months And Days

Sep 13, 2009

I am using the following formula to calculate years months and days in Excell 2007
=DATEDIF(C7,D7,"y") & " yrs, " & DATEDIF(C7,D7,"ym") & " mths, " & DATEDIF(C7,D7,"md") & " days"

C7 3/24/04
D7 1/4/08

What is returned is 3 years, 9 months, 136 days

How might if fix it show the correct days?

View 9 Replies View Related

Time Elapsed In Years, Months & Days

Oct 23, 2009

I have a start date dd/mm/yyyy, and require a formula that everytime a spreadsheet
is opened, based on the current date, to calculate how many years, months and days
have elasped yy mm dd since the start date...

My date formula knowledge is a little limited.

View 9 Replies View Related

Counting Occurences Of Months And Days Etc In A List

May 11, 2006

I have been struggling for almost 2 days with this problem.

I have a list of data, one column of which is the date displayed in dd/mmm/yy format.
This date will always be entered by users and is variable.

I can't find a way of counting the number of occurences of each month and each day that the date represents.

So, I want to know how many occurences of March, June, May, Septemeber etc are on this list and Mondays, Tuesdays, etc.

I have tried several posssible routes, DCOUNT DCOUNTA COUNT COUNTIF SUM. I've also tried separating the date out into days & months using the MONTH & DAY functions but this didn't work either.

It also apepars (shock horror) that Excel has incorrect date & day values because entering todays date in one cell (11/05/2006) and then using the DAY function to find the day of the week for this date produces WED when it should be THU. (My system date is set as 1 Jan 1900).

I feel that Excel can't separate the months & days away from the date, since the date is stored as a numerical value and not as we humans use dates.
The dates will always be manually entered by users, probably as dd-mm and Excel will automatically add in the year.

How can I count the occurences of each month and days of the week?

View 9 Replies View Related

Date Differences In Days, Weeks Or Months

Jan 3, 2008

I've been racking my brain on this one for awhile, and I feel like I'm going the right direction but...

I have a list of dates like:
11/30/2007
10/31/2007
9/30/2007
8/31/2007
7/31/2007
6/30/2007
5/31/2007

or maybe

11/30/2007
11/23/2007
11/16/2007
11/9/2007
11/2/2007
10/26/2007
10/19/2007
10/12/2007
10/5/2007
9/28/2007
9/21/2007
9/14/2007
9/7/2007
8/28/2007

...and I need to determine if the difference between them is daily, weekly, or monthly. There are 79 of these lists, on 79 different sheets so I can't visually peg them as daily, weekly, or monthly.

The way I have been looking at the problem is to calculate the number of months between the last & first dates and see if the resulting answer (+1) matches the number of actual dates, and so on...

View 5 Replies View Related

Macro For Base Amount And Net Amount Calculation

Nov 20, 2012

For the below macro.

ABCDG
CodeTotalBaseNetA1 = 20%
A110083.3316.67A2 = 10%
A2350318.1831.82A3 = 7%
A3220205.6114.39A0 = 20%
A015015030

A1 is the code, take the total(B) and calculate the base(C) on the percentage given in the G, net = B-C. for A2 and A3 it is the same process.

For A0 the total (B) is equal to Base(C) and calculate the net on the given percentage in G.

View 1 Replies View Related

Excel 2013 :: Autofill (for Days And Months) Not Functioning

Apr 3, 2014

I am using excel 2013 in my laptop (windows 8.1) after having upgraded excel 2007. Everything works fine during my daily tasks. Especially autofill which I use quite often works perfect.

At the same time I upgraded my girlfriends laptop (Windows 7) with the office 2013 (office 2007 was the older version) and somewhere there begun some isues. Autofill doesn't. Especially with months or days. I enter the value "Monday", I drag down (of course using the black filled cross down right of the cell) the cursor and all the cells get the value Monday. Even if enter the value Tuesday in the second cell and then try to autofill (having marked before both these two cells) I get the same results. Monday, Tuesday, Monday, Tuesday.

I tried to test the autofill with numbers. If i write only the one number (for instance the value "1") and then autofill, this doesn't work. It gives me the value "1" in all the cells. But if I enter the value "2" in the second cell, the autofill works fine (marking these 2 cells and then autofill).

We make some online courses using exactly the same excel exercise files. In my laptop everything works perfect.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved