I'm working on a report where i need to find the last date a payment was made by a certain client. The problem is that not all clients have the same amount of payments so I cant just choose a payment number in my formula.
Trishlast payment.xlsx
I'm currently developing a model for a payment schedule. For example, lets say a customer purchases an item from a store, but the store receives that payment in the following month. I'm looking to develop a dynamic model so that if I change the assumption from 1 month to another term (2,3,4+ months etc.) the model will adjust accordingly.
I've started with a formula
=IF(MONTH(H3)-MONTH(G3)=B14,G11*B13)
But of course this will not work once the term is set to larger than 1 month. With B14 representing 1 month, and G11*B13 being payment information (price * quantity).
I have a list of several hundred loan recipients who are all starting to pay their loans back on different dates. I am able to get the calculation to work so that I just need to put in the start date and excel works out the other 5 or 6 payment dates.The problem is, some people pay back on the 29th, 30th of the month which is giving me a headache when they are supposed to pay in February. The formula I am using calculates the next date of payment to be on the 3rd March (skipping the February payment all together).
The formula I am using is : =DATE(YEAR(A10),MONTH(A10)+1,DAY(A10))
How can i get the actual Payment due date from no. of days using macro. i. e.
Posting Date Dues Payment Date
------------ ------ ---------------
22.06.2013 21 days ???
i am trying to get the number of days from last time ticket paid till todate, considering the day & month of the hiring date but the year of the last time paid. say AAA hired on 15-Nov-2001 receives a ticket every 24 months; received last ticket in Nov 2011.
i want to count the number of days between 15-Nov 2011 until 31-Jul-2013 (dd & mm are from joining date yyyy from last time paid) i have a list of 1200 names with different dates of joining and different dates of payments.
When a date is entered e.g 1/27/2007 The bill payment dates should all be automatically calculated under the “Date” column. Rules for the date
Rule1. e.g. 1 Month—the date should be 1 month after the date entered (as shown by the simple example 1/27/2007 one month is 2/27/2007 in the sheet)
Rule2 if the date entered is the last day of the month, the calculated date should also be the last day of the month provided it is a working day.
E.g 1/31/2007 one month is 2/28/2007. (provided 2/28 is working day)
2/28/2007 one month is 4/30/2007 (provided 4/30 is working day)
Rule3 If the calculated date based on rule1 and rule2 is a non-working day, the date is pushed back by 1 till it becomes a working day. e.g 1/27/2007 two month is 3/27/2007 if 3/27/2007 is Saturday it becomes 3/28/2007 which is a Sunday, another non-working day. So the output should be 3/29/2007
Rule4, under rule3, the date cannot be pushed to the next month. Instead, we push the date forward. In the previous example assume 3/29, 3/30 and 3/31 are all public holidays, by rule3, it will be finally pushed to 4/1. But this becomes another month. So we push forward instead to 3/26 which is Friday. The output is 3/26/2007
Definition of working dates (Monday to Friday excluding the holidays listed in the sheet).........
I have a schedule database the one I need to transform some cell values in time format. The thing is that I have the format as follows, and I was trying to set a macro to convert every "*" in 15 min value without succcess.
Example
Associate below works as folllows:
Sat 3:30 - 8:30 Mon: 4:00 - 8:00
Sun 2:30 - 5:45
*(start) represents 0:15 min value.
I need to transform the *'s and sum the time format and place the value below the START and STOP column.-
Note: (disregard dots)
Agent: Julia Roberts
Day Start Stop 00:00 1:00 2:00 3:00 4:00 5:00 6:00 7:00 8:00
Sat.............................................** **** **** *** **** **
Sun.....................................** *** **** ***
Mon ...............................................**** **** **** ****
etc
Each row of the spreadsheet from a6:a25 is the planned production of one cellbuilder.
From this sheet I need to produce a work schedule of tasks to be performed by date.
Detailing, batch, cellbuilder number,weather 1st or 2nd graft etc, and the date available (for picking)
I would like the option to choose a daily or weekly schedule.
Is this possible ? and how would I even start to go about it. I just can't visualise how to do it.
I would like a simple way to make a single column whose rows are sequenced Mon, Wed, Mon, Wed, ... and have the correct dates. For example:
Mon 2/11
Wed 2/13
Mon 2/18
Wed 2/20
Mon 2/25
etc.
Cell A1 could have Mon 2/11, Cell A2 would have Wed 2/13, and so on. (20 to 50 rows)
The ordinary Fill Series when applied to Mon Wed will continue with Fri Sun Tue Thu and so on, or it will return Mon Wed Mon Wed Mon ... with the wrong dates. Also, the solution should be applicable for a Tue, Thu(TTh); or Mon, Wed, Fri(MWF); or Mon, Tue, Wed, Thu (MTWTh) series.
I have created a daily schedule which has a number of factory variables taken into consideration which determine the date and time a particular product should, barring any mechanical problems, come off the machine. (see attached spreadsheet).
The date at the top will be editable by me only so that when I update the production quantities, the “date/time off” column automatically re-adjusts to the remaining quantities.
The formulas are a little long winded, but I have left them that way whilst I try and develop it. I should be able to figure out how to condense them later.
My problem is that the “date/time off” on the right works excellent, but over a 24 hr period.
Ordinarily, we work a 12 hour day (6am to 6pm) with overlapping shifts to cover breaks, and 20 mins warm up at the start of the day for the machine, thus maximising a 12 hour day.
Of course if demand exceeds the allotted time we put on overtime.
Is it possible to specify that normal days are only 12 hours so that if a product exceeds 6pm, it flows into the next day with the balance starting at 6:20am?
And, if the production for the week exceeds the time could I stipulate particular days which we deem are suitable for overtime? Ie, we decide Wednesday is a 14 hour day and not 12.
I had toyed with the idea of creating a 365 day table/calendar, on another worksheet which would have its individual allocated hours in an adjacent column and somehow link them to the date/time off, perhaps by way of a VLOOKUP, but I have been chasing my tail trying to figure out how to implement it.
This is a new payment status sheet that my company has put together to use with future clients. There are a few things that we would like fixed but can not figure out ourselves. I have attatched the document along with some notes as to what we can not get to work.
View 14 Replies View RelatedI would like to be able to choose a month (from a drop down menu), then choose who the payment will go to ie. creditor (from a drop down menu) ie. car, truck, cc payment, as a result of those selections, then enter the amount of the payment, and finally the ending balance will appear. I have the drop down menus in place and I have all the balance calculations in place. I could use the spreadsheet that I have but I only want to see the below info. Plus I don't know how to link or lookup this data to the stuff I've already created.
Select Month:
Select Creditor:
Enter Payment:
New Balance:
I have already set the sheet up calculating the balances after a payment has been made. I know how to hide all the formulas, but I don't know how to get it to so that you choose a month, then creditor type (from drop down menus), and payment so that you see a new balance. I only want to be able to link or lookup the month, creditor together so that a payment can be made to that cell that I've already established. As a result, then that cell can be shown in the new balance.
Not sure if this is the correct section for this kind of query but I'd like some assistance with a calculation that I can't seem to figure out.
Essentially it's for calculating night payments for our employee time-sheets. Our staff have very sporadic shifts and are paid extra for working between the hours of 00:00 and 06:00, basically when employee's enter their start and end times I'd like the spreadsheet to automatically calculate how many hours they have worked between those hours, I imagine it's very simple but I cannot figure out which function to use.
To complicate matters, because staff can work shifts which start on one day and finish the next we work on a 48:00 clock basis so its' not only between the hours of 00:00 - 06:00 where they qualify for night payments but also from 24:00 - 30:00 if that makes sense?
I am trying to set up payment record sheet as follows
First Tab: This is the total value of each item, with the payment date in cell C1
Second Tab: Is the payment date of the item
Third Tab: Is the payment due tab
So what I want to do is enter the payment in C1 on the first tab. Then on the second tab I want to enter all the items that want paying on that day, but this will also have previous payment dates shown as a sort of record of what was paid on what day.
On the third tab I want it to show all the payments that correspond to the same date as C1 DATEVALUE
And on the gross tab I want to show all payments to date including the current date.
I have got the payment date to work but I cant get it to show all the previous values.
I have a situation where I need to highlight different customers based on what there terms of payment are. Conditions 1 and 3 seem to be working fine but for some reason condition 2 is not working at all. I made sure all names are exactly as listed in the A2 cell....
View 9 Replies View RelatedI have been raken by brain for a simple formula to calculate an Interest Only Payment on a mortage note, example,
sales price = $162000
term = 360
interest = 8.5
I was able to calculate the payment of principle and interest, but I need to validate what would be an Interest Only payment on this?
I used erecord the other day to do my activity statement for the first time (I have just started a home based business which has not yet started trading but I had to send the BAS for the purchases that I had made for the business) and it was quite easy to use and you can send it electronically to the ATO which saves a lot of hassle particularly as I am very not accountancy litterate. However I am trying to develop an expenses/payment spreadsheet similar in function to erecord but that allows me to categorise the inputs.
My headers are:
Date
Cbookref = (drop down validation box) similar to a chart of account #
Category = ie - advertising accounting fees etc.. uses a look up function with cbook ref to populate field
Description .........................
i have myself a table on the left is all the members names and along the top are 12 dates (1/1/07, 1/2/07 etc).
In another sheet i have a drop down box for the name of the person and a dropdown box to select a date and then a space to enter the amount paid so i need a macro that will find the name and date i selected from one sheet and take the amount paid into the other sheet and insert the amount in the correct place.
If it is unclear what i mean i will post screenshots tomorrow.
I have a file which student Monthly Loan Payment schedule.
I want to make a list which shows all payment period by installments and payment amount , dates by Student names.
Attached file, you can see all detail and wanted LIST.
How to calculate a One time Payment to maintain the Tenure In Amortization?
Ex:-
I havea loan of Rs 1,00,000/- to be paid @ 8%/12 for the first year and then from 13th month , it will be Floating Rate of Interest which would be mostly higher than 8%/12...
I will be paying this loan off in 5 years ( i.e. 60 months)
and the EMI = Rs 2028/- per month..
Lets say, if the Interest goes up to 12%/12 after the 12th EMI then the tenure increases by 5 months from 60 months to 65 months thus adding to the cost of Rs 2028*5 = Rs 10140 more..
Now What amount do I need to pay alongwith the regular EMI's to maintain the tenure of months?
The amount Im referring to is a One-Time Payment added with the EMI for the 13 the mOnth to reduce the Principal and accordingly the tenure?
I have a capital lease amortization schedule with annual increases to monthly rent that I am trying to solve for an interest rate such that the balance nets to zero at the end of the term. I am calculating on a monthly basis; in other words, principal minus monthly payment plus monthly interest expense equals ending monthly balance is calculated in each row each month. The present value is known, payment term is known, future value is zero, and the payment amounts increase annually. These assumptions may change for new leases so ideally the solution would be dynamic, adjusting for shorter/longer terms, etc. Here is an example of my assumptions:
Payment start date: 8/1/13
Term end date: 10/31/2025
Rent length in months: 144
1st months rent: $500,000 - payments are due at the beginning of the month and are paid monthly
Annual rent escalation: 3% - i.e. 1st 12 months is $500k/month, 2nd 12 months at $515K, etc.
Beginning NPV: $75M
Ending value: $0
Imputed annual interest rate: UNKNOWN
I'm not sure if this is relevant, but the monthly payment is allocated between principal and interest. Monthly interest expense is calculated as the current balance * (imputed interest rate / 12).
Currently, I've plugged the interest rate such that my ending balance is 0, however I was hoping to calculate it on the fly as opposed to manually plugging it.
how to calculate annual interest rate with these inputs? Is there a way to make the rate function work with payment increases?
I'm building a real estate payment calculator. I'm using the Payment (=PMT) function in Excel 2003. When I add the information that it asks for in its wizard, I receive the correct numerical figures but with a minus sign in front of it making the result negative, i.e. "-$630.13" instead of "$630.13."
Is this file corrupted or have I put something in incorrectly?
I have 2 production unit : A; B
They make payments to suppliers on certain periods : p1, p2..
The payments are on variable and different currencies : EUR, SEK, USD, CHF,AUD..
I explained the sample case table in attached excel sheet. My question is "how can i create the summary table easily and quickly by excel?"
currently I am calculating it by a simple excel formula (vlookup) that I need to change the range for each time. It s very time consuming and easy to make mistakes..
I have a list of employees who's job status may change at the end of a given month. If the status changes, they have a supplemental payment that is tied to their job status and may change as well. But that depends upon certain conditions like their years of service and their union.
I'm trying to write a formula that will update that supplemental payment based upon certain conditions, but I can't quite seem to get it right. A challenge is the table I'm looking values up in has 8 columns and the value could be in any of the columns depending on years of service.
These are the conditions for the lookup:
1. If their union local is the same, they would keep the supplemental payment so long as it is not more than allowed for the title. If it is more, they get the supplemental that corresponds to the new title and the years of service.
2. If the new title is PAA and they have 10 or more years of service, the new supplmental is $600.00
3. If the new title is Clerical Associate II and they have a 6 or more years of service, they get a supplemental that corresponds to their years of service.
Salesman
Sales Amount
Introduced by
sales
introduced
sales
indirectly introduced
sales
Tom
300
none
Tom
300
[Code] ........
I have salespeople who I want to pay a bonus to but I've also offered them a bonus on how much the salesmen they referred to the company sell and this goes like a pyramid down 3 levels.
so I have a list of salesmen next to there name is how much they have sold then who introduced them. from this I need to make a list of each salesman and below all the people he introduced and the people they introduced with the sales in the cell next to them.
To show how it could look I've done an example above of the information I need for tom, as you can see all the data is in the first 3 columns and tom needs to show that he introduced bob & Sue, but because Bob & Sue also introduced someone, tom needs to show he was involved in this as well and jo, terry, mark need to show, preferably in a different column as the bonus for them is lower.
I'm trying to combine the hours that a employee worked on a single date, with one of multiple time periods that exist for that employee.
I have two sets of data.
Set 1 (hours)
Employee number, date, hours
12345, 1-2-2014, 6
12345, 1-3-2014, 8
12345, 1-10-2014, 8
Set 2 (periods)
Employee number, start date, end date
12345, 1-1-2014, 4-1-2014
12345, 6-1-2014, 1-2-2014
What I'd like to do is to add the start and end date of Set 2 to Set 1 for every row in Set 1
In above example the result should be like this.
12345, 1-2-2014, 6, 1-1-2014, 4-1-2014
12345, 1-2-2014, 8, 1-1-2014, 4-1-2014
12345, 1-10-2014, 8, 6-1-2014, 1-2-2014
I have collected some data on economic factors for different countries. Unfortunately, the dates when I started to calculated my economic factors are different for each country (due to the data available to me).
What I would like Excel to have done is to take the date when I started to measure for e.g. country A (D3 ie 30/06/2007), copy it into column "I" (for country A, it's cell I3) and fill in the following months in the rows below (with always the date of the last day of a month) until it reaches 28th of Feb 2013. Then, it should go up to the next country (country B) take the starting date (D4, ie 31/07/2007), go to the last entry in "I" (ie I71) and paste the date in, fill in the months until 28th of Feb 2013, do the same for country C and so on.
I have started to code a VBA but I am unfortunately a beginner in VBA and totally stuck at the moment. My VBA code does paste in the months but for some reason, it also changes the starting date of the first month.
Moreover, I tried a workaround for the fact that Excel doesnt know when to stop; ie I introduced a "monthdiff" variable which should calculate the number of months between the starting date (which is variable and unique for each country) and the end date (which is always 28th of Feb 2013). At the moment, it only does this for country A.
VB:
Set rng = ActiveSheet.Range("I3" & Cells(monthdiff, "I").Address)[SIZE=4][/SIZE]
I have tried to make this dynamic but have been unsuccessful so far.
Spreadsheet with data&code is attached.
VB:
Sub Macro1()
Dim mainrange As Range
Dim rng As Range
[Code].....
If a sale was made between 2/09/2014 - 2/15/2014 return the date of the following weeks Friday. In this case 02/21/2014.
View 3 Replies View Relatedway to search and display a date from a range of cells based on less than or greater than criteria. For example I have following dates in column A:
A1: 2011/01/04
A2: 2011/02/01
A3: 2011/03/01
A4: 2011/04/01
I want to search for the date which is less than 2011/02/01 from the A column and display that date in B1 cell for example. How do I do that?
I want to do this without using any macros.
I need a formula thet will look at the last date of the absence and calculate what would be the last working date in the given financial yaer.
Example
Abcence Start Date 21/08/13
Abcence Due End Date 04/11/14
Financial Year Start Date 01/07/13
Financial Year End Date 30/06/14