Calculating Date Of Payment?

May 2, 2014

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))

View 3 Replies


ADVERTISEMENT

How To Find Last Date Of Payment Schedule

Sep 30, 2013

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

View 6 Replies View Related

How To Calculate Payment Due Date From Number Of Days

Jul 7, 2013

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 ???

View 9 Replies View Related

Counting Number Of Days Between Anniversary Day And Payment Due Date

Aug 11, 2013

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.

View 6 Replies View Related

Bill Payment Calendar Should All Be Automatically Calculated Under The “Date” Column

Jan 27, 2007

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).........

View 3 Replies View Related

Calculating Age From Date Of Birth

Sep 16, 2009

how to calculate the age from the date of birth and was given these two responses

1) I may be way off here but all that coding seems a waste !

I have in cell A1 = BLANK <--- enter your DOB here

I have in cell A2 =NOW()

in cell A3 do = A2-A1

Format that to General

In Cell A4 do =A3/365

Format that to no deciamal places and you get the age - this one is adding a year to everyones age?

2) Alternatively, if the DOB is A1 :-

=INT(YEARFRAC(A1,TODAY(),1)) - This one does not work it is saying there is an error with the "yearfrac"?

It is also when I am trying to put these into a pivot table, the pivot table is listing these down to 5 decimal places( when I have formatted to zero)????

View 2 Replies View Related

Calculating Due Date From One Of Two Cells?

Mar 25, 2014

At the moment I have a simple formula that calculates the due date of a task

The log in date is in cell B3, I use a simple formula in cell F3 "=B3+20" - This gives me the due date

I now need to add another log in date but use the same due date cell

So now ....

Either B3 or D3 will contain a date - whichever one is populated I will need the due date displayed in cell F3

View 10 Replies View Related

Calculating Age When Given Only Date Of Birth

Sep 6, 2005

http://www.cpearson.com/excel/datedif.htm
--

"malvis" wrote:

> Is there a formula that can calculate age when given only Date of Birth?

View 14 Replies View Related

Calculating Max Date In A Range?

Oct 13, 2011

I have a need to calculate the latest and earliest date in a given range, but I cannot seem to make it work properly. The range looks like this:

Code:
23/08/201120/08/2011 13/08/11 13/08/11 13/08/11 13/08/11
and the code is:

Code:
d1ST = Application.WorksheetFunction.Max(rRow)

No matter the information in the range, the statement is returning "0" (or "12:00:00") - I have tried setting d1ST as a date and double data type.

View 1 Replies View Related

Calculating Future Day And Date

Nov 24, 2012

I think I may have over engineered this formula.

$F$40 = Cell with Event Date

=IF(WEEKDAY($F$40,2)

View 2 Replies View Related

Calculating The Date At The End Of Each Week

Apr 24, 2007

I am trying to create a formula that returns the date on the friday (ie last working day) of whatever week I am currently in. So, for example, I guess I would use the 'TODAY' or 'NOW' function somewhere to determine the present date - but how do I then determine the date on the Friday of the current week?

View 9 Replies View Related

Formula For Calculating End Date

Oct 4, 2009

I was trying to figure out how you could caluculate a project end date, based on a start date (dd,mmm,yyyy) and a project duration of lets say 5 work day and the work week is five days long (monday to friday). If i have a leave on the week days like tuesday is bank holiday then it should give be the date excluding that tuesday and wednesday leave and caluculate 5 working days and shoule give the project end date.

Example date: 5-Oct-09

Project end date:09-Oct-09

If i have bank leave on 06-Oct-09 means.

My project end date:12-Oct-09

Note:Bank holidays are fixed date example(every year bank holiday 06-oct-09,02-Dec-09,25-Dec-09)

Please dont need a macro.

View 9 Replies View Related

Calculating Friday Date

Aug 7, 2006

I have dates listed in column A indicating the day an action was performed. In column B I need to have the date in column A changed to a Friday. If the date in column A is not a Friday, then the date in column B needs to fall back to the previous Friday. For example, if the date entered in column A is today, Monday August 7, then the date in column B that I would be Friday August 4. Can anyone think of code that determine this and place the value in column B? Currently I use vlookup to go to another sheet where all the possible dates are listed with their respective Fridays, but this slows down the calculation worksheet as the file is quite large.

View 9 Replies View Related

Payment Staus Sheet

Dec 2, 2008

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 Related

Entering A Payment And Seeing A New Balance

Apr 9, 2009

I 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.

View 14 Replies View Related

Night Payment Calculator

Feb 8, 2010

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?

View 9 Replies View Related

Set Up Payment Record Sheet?

Aug 12, 2013

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.

View 1 Replies View Related

Model For Payment Schedule

Aug 20, 2013

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).

View 2 Replies View Related

Conditional Payment Terms

Aug 21, 2008

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 Related

Interest Only Payment Formula

Aug 24, 2006

I 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?

View 5 Replies View Related

Payment Expense Spreadsheet With Gst

Nov 3, 2006

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 .........................

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

Calculating Next Due Date For Rental Equipment?

Feb 15, 2014

I have a contract start date. The payment is due every seven days. I have entered the start date. i need the "next due date" to automatically calculate in the "due date column". here is my problem if a customer has a due date say on 2/15/2014 his next due date should be 2/22/2014. regardless if he pays on the 14th or 21st. i need this date to change each time a payment is made.

View 7 Replies View Related

Calculating Several Data Depending On Date

Jun 12, 2009

I have a truck log. Every time some comes in to take a truck they must log the truck out with the millage. After a week, I am supposed to find out how many miles my trucks ran for that week.
What would be the code to....

In K column, calculate the milage that is in row F; which is A3 - A2 and so on down. But it would have to skip a line every time the truck number changes (in column C) it would skip that line. It would go until a blank cell.

Does anyone have any ideas how to do that?

View 10 Replies View Related

Calculating Date AND Time Differences

Mar 21, 2006

Within 4 columns, I have a series of:
Start Dates / Start Times / End Dates / End Times
I need to create a formula which will give me the sum total of the difference between the data sets.

For example, the time difference between 8am on the 20th March and 1pm on the 21st March is 29 hours OR 1 day and 5 hours.
I need a formula which will calculate this for me.

View 11 Replies View Related

Calculating 10th Date From List

Sep 20, 2008

I'm using the following formula to find the 1st date that a particular rep wrote an order.

{=IF(S4="","",IF(MINA(IF('Daily Compliance'!$A$2:$A$5028=B4,'Daily Compliance'!$O$2:$O$5028))=0,"",MINA(IF('Daily Compliance'!$A$2:$A$5028=B4,'Daily Compliance'!$O$2:$O$5028))))}

I also need to figure out when they write their 10th order, but have had no success figuring that out.

View 6 Replies View Related

Calculating Dates Until 16 Days From Date

Mar 9, 2009

I am trying to calculate dates that will happen 16 days from date specified in column A (A23:A200), based off a certain entry in Column B (B23:B200), which is named in cell P5. I can get the date by doing the specified date + 16, e.g. =IF($B$23:$B$200=$P$5,$A23+16,"")

The trouble I am running into is not having the date populate until it has reached 16 days from the dates in column A, show blank if possible until 16 days from date in column A. The dates in column A will always be previous to todays date.

View 2 Replies View Related

Calculating Date And Time In Excel?

Sep 14, 2011

I am trining to calculate SLA times based on P2, P3 & P4 calls, the only issue is with P2's as the SLA includes the weekend, how to include the weekends into my formula?

AE2 = Priority
U2 = START DATE & TIME
V2 = END DATE & TIME

[Code]....

View 4 Replies View Related

Adding A Payment Based On 2 Values

Mar 12, 2007

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.

View 12 Replies View Related

Calculating Days In A Month Between 2 Date Ranges

Mar 14, 2013

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].....

View 3 Replies View Related







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