Excel 2003 :: Amortization Table - Years Elapsed From Column Of Dates
May 21, 2012
I am working with an amortization table and need to work out the elapsed time to amortize the loan.
Currently the worksheet is working fine and calculates the end date of the loan but the length of the column varies depending on the scenario.
I need a formula to calculate the time that elapses between the first date and last date.
The data starts in cell b13 and the last date could appear in pretty much any cell below that, so the formula will need to look for the last valid entry.
I am using excel 2003.
View 5 Replies
ADVERTISEMENT
Feb 25, 2013
I am using Excel 2003 and I created an amortization schedule set up for an debt account. I am trying to pull the "Balance Due" from that schedule into another chart based on the current date (these are on two different sheets in a workbook).
For example, this is my 'Amortization Schedule':
Balance
Due
Interest
Rate
This Month's
Interest
This Month's
Payment
[code]....
And I am trying to pull the "Balance Due" from that schedule to place into this chart on my 'Debts' sheet: (based on the current date)
Name
Starting
Balance
Remaining
Balance
Interest
Rate
Minimum
[code]....
For example, if today were 1/15/13, I would want $3,796.34 from the schedule to go where the "x" is on the chart above. What formula would I use to accomplish this?
Also, on a side note, would there be a formula to have Excel pull the "Payment Date" from the schedule into the "Payoff Date" in the chart based on where the row has a $0 Balance Due?
View 2 Replies
View Related
Apr 3, 2014
I have been trying to get excel to calculate elapsed time between two dates using mostly what I've read from other elapsed time posts. But I am clearly missing a step. I'm trying to streamline my process for taxes.
I want to take a time in the format of " yy/mm/dd hhmm " example: 1
3/04/24 2245 ( note its a 24hr clock) 13/04/24 2245 would represent 2013 April the 24th at 10:45PM.
------- A --------------- B -------------------- C
13/04/24 2245 ------13/05/24 0900 ---------[display hrs elapsed] <-- currently get #name?
I have custom formatted A and B to yy/mm/dd hhmm and formatted c to [h]:mm
When you enter b-a in column c you get #name?
If its easier to have the date and time into separate columns I can give that a shot, I was just trying to make it as streamlined as possible for copy past.
View 11 Replies
View Related
Mar 10, 2014
I am trying to come up with a formula to determine if someone is 21 years old or not. After I enter their DOB if they are 21 or over I would like the cell to turn Green, if not I would like it to turn red. I have tried alot of different formulas that primary pertain to days and months but nothing for years.
View 3 Replies
View Related
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
Aug 30, 2012
Using Excel Office 2003.
I have been using VBA to copy and move files to create backups for 7 or 8 years now, and it works great. However, recently I have been working on a way to use SD cards for the backups and the FileCopy command is much slower than using Windows Explorer.
I am looking for some alternate command lines that might do the job faster. I would like to know what Explorer is using.
View 8 Replies
View Related
Dec 3, 2012
I have:
Excel 2003ABCD1StockDatePriceReturn2MSFT31-Dec-1027.91-6.97%3MSFT31-Dec-093054.32%4MSFT31-
Dec-0819.44-45.39%5MSFT31-Dec-0735.619.22%6MSFT29-Dec-0629.8614.19%7MSFT30-Dec-0526.15-2.13%8MSFT31-
Dec-0426.72-2.37%9MSFT31-Dec-0327.37111.84%10MSFT31-Dec-0212.92-21.98%11MSFT31-Dec-0116.5652.77%12MSFT29-
[Code] .......
and would like:
Excel 2003ABCD1Sum of ReturnStock2DateBAINTCMSFT331-Dec-970.83851.14150.5659431-Dec-98-0.33350.68793.2921531-
Dec-990.27041.77732.3668629-Dec-000.59270.4606-0.6286731-Dec-01-0.41240.04620.5277831-
Dec-02-0.1493-0.5049-0.2198931-Dec-030.27741.05841.11841031-Dec-040.2285-0.2702-0.02371130-
[Code] ...........
Code:
Sub codedPTable()
Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("Data")
[Code] ..........
Only prints this:
Excel 2003ABCDE1Sum of ReturnStock2BAINTCMSFTGrand Total3Total1.99894.69867.331214.0287Sheet2
If you have a more compact macro than what I tried to change above even better (the data is in the simplest testing format).
View 4 Replies
View Related
Apr 7, 2012
I am using 2010 to write some code for a spreadsheet that will be used in 2007, so I know that RepeatLabels is not available.
My question is if you Group a date field by Months and Years and the data spans across 2 or more different years, is there a way to hide months in a specific year? I ask because the Date field has only 12 months in it and if I hide Jan (for example) it hides Jan in both years. (attached pic of filter for Date field below.)
Is there some other way to format it so it can discern between years or do I need to add yet another column to my original data?
View 2 Replies
View Related
Dec 18, 2013
how to calculate the time between two dates in years and quarters (represented in .25 increments). I would round down on the .25 increments (3 months) if not fully surpassed.
See attachment on what it should look like. C2 is my calculated column.
View 4 Replies
View Related
Jun 8, 2014
I need to add 65 years to a column of birth dates and the result must be in yyyy/mm/dd format. What is the formula?
View 11 Replies
View Related
Jan 10, 2010
I am trying to use the if function in an amortization table and I am wondering if there is a way to make it so if I cut the years in half if I can make the table read with 0's or dashes rather than to start counting back up again.
View 9 Replies
View Related
Apr 5, 2007
I need help creating a formula. I have an amortization table and I need to take each months information and place it into a cell which resides in a letter. In the end, there should be 180 pages with different dates, interest, principal and total payments. I know that there is a way and I can not figure it out.
View 9 Replies
View Related
Jun 1, 2008
I'm trying to make an amortization table with all the individual payments. I'm trying to avoid putting in the formula manually into each individual cell. I have a formula = ROUND(F11*0.12/12,0) and I want to insert that formula down an entire column, but increasing the F11 to F12, F13, etc.
View 4 Replies
View Related
May 23, 2013
I have a sheet (like example attached). it's broken out by month for many decades. certain columns like B-N need each field populated. There are others like O, P, Q, where I need to delete all cells except for 2 months that year (in this example, all except May and November).
Is there a shortcut method to do this quicker?
example.xlsx
View 3 Replies
View Related
Jun 18, 2014
I need to know the formula and/or codes to create a trigger on my excel worksheets to send email when an item needs follow-up. The workbook was created on 97-2003 but the outlook program I believe is a later version.
How I can create the email triggers. I am enclosing a copy sample of the spreadsheet I need the Macros attached to (I will need to know how to create/run macros with my spreadsheet. What I need is an email triggered to the sales rep based on the follow-up date. The email should include the quote number and customer name.
Testformacros.xlsx
View 6 Replies
View Related
Nov 8, 2012
I'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.
If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.
I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.
View 1 Replies
View Related
Apr 18, 2013
I'm using Excel 2003.
I have a Userform where you can enter a date and select a comparison operator from a combo box. Any one of these (= < <= > >= <>)
In VBA I then load a range into a dictionary object and then loop thru it to compare the dates in the dictionary to the date selected in the userform but I can't get the dates to compare correctly. I think it has something to do with the Evaluate function comparing the dates as strings but I'm not really sure.
VB:
For Each strKey In dictValid.Keys()
If Not Evaluate(CDate(dictValid(strKey)) & cboDteOperator.Value & CDate(txtDteSel.Value)) Then
View 3 Replies
View Related
Nov 22, 2011
I'm using Excel 2003 & Windows XP Professional.
I have two sets of data tables. One contains Number, Name & Date. The other contains amounts which those people have paid in 2007, 2008, 2009 & 2010. So in the below Jones has paid a total of $580 over those years, Smith has paid a total of $650 over those dates & so on.
The amounts in 2007, 2008 etc are full year amounts. I'm after a formula that can calculate the amounts for part years based on the dates from C1 to C11 by number (or name).
I've got what I think the results should be (I think with some rounding problems), but my real data has much more data.
View 3 Replies
View Related
Apr 11, 2012
I have been struggling to get dates to remain in a consistent format within my Excel 2003 worksheets. I would like to work out how to keep them permanently in the "dd/mm/yyy" format. I have tried several formatting options. The current method I will explain below:
I have several forms into which the 'date' is automatically entered using the - Format(Date, "dd/mm/yyyy") code. This is then saved into cells on a worksheet using the code
ws.Cells(iRow, 1).NumberFormat = "dd/mm/yyyy"
ws.Cells(iRow, 1).Value = CDate(Me.Date1.Caption)
At this point if I do a search for a date, lets say 23/03/2012 I can retrieve it and everything related to it - all seems fine (also all dates are visually in the correct format in my cells etc).
The problem arises when I exit Excel and save the workbook. When I go back into the workbook at a later time and try and search for the same date it won't work. Although the dates in the cells still read for example 23/03/2012, the search requires me to now use the format 03/23/2012 to retrieve the information (It seems like in the background the date has reverted to the US date format upon saving and yet visually it is still how I've set it up).
View 1 Replies
View Related
Dec 13, 2013
test.xlsx
I need to calculate the time elapsed between start and finish dates. I've figured the TODAY function, as explained here but I'm referring solely to dates in cells. How to?
View 1 Replies
View Related
Mar 5, 2009
Dataset is 220K rows. A small subset follows.
Using an array calc with criterions Day = 1 and Hour = 1 summed on Elapsed Time returns 81 with an average of 11.57 for 7 events of which there are 5 distinct dates.
The function =SUM(IF(D2:D14<>"",1/COUNTIF(D2:D14,D2:D14))) returns the number of distinct dates in the dataset ie 7.
Is it possible to combine these two methods to create an average of elapsed time by day and hour using a denominator of distinct dates for only that combination of dimensions?
I have attempted to calculate an item in a pivot table using a different denominator without success.
day Hour Date Elapsed Time
1 0 5/03/2009 25
1 0 5/03/2009 24
1 1 5/03/2009 23
1 1 7/03/2009 11
1 0 7/03/2009 5
1 1 8/03/2009 2
1 1 8/03/2009 8
1 1 9/03/2009 4
1 1 9/03/2009 17
1 0 9/03/2009 22
1 1 10/03/2009 16
1 2 13/03/2009 12
2 1 14/03/2009 15
View 4 Replies
View Related
Jun 18, 2009
I must produce a report that details elapsed time between two dates and times.
The duration can span a number of days, I need to report just the elapsed working time, I can calculate the days with "NETWORKDAYS" but how can I calculate only the elapsed working time 08:00 to 17:00?
View 9 Replies
View Related
Sep 17, 2008
I have a VBA subroutine that builds a list of dates in an array and then copies this to the worksheet using worksheetfunction.transpose. The array is two dimensional and therefore uses the variant data type.
This works fine in Excel 2002. In Excel 2003 the array is fine until the Transpose function is used, at this point they are changed from dd/mm/yyyy to mm/dd/yyyy. This means that some dates are changed (eg: 01/Mar/2008 becomes 03/Jan/2008) and some are written as strings (eg: 17/Mar/2008 becomes the string "17/03/2008" and cannot be operated on as a date).
I'm aware that there was a hotfix to deal with a similar issue regarding recalculation but this is on Excel 2003 SP3 which should already have that hotfix in place.
I have reduced my code down to a basic demonstration showing the problem. This assumes the worksheet contains some dates in "A2:A32". I have also attached the workbook containing this code.
VB:
Private Sub CommandButton1_Click()
Dim a() As Variant, c As Integer
Redim a(1 To 31)
For c = 1 To 31
a(c) = Cells(1 + c, 1)
Next c
Range("D2:D32") = WorksheetFunction.Transpose(a)
End Sub
I have fixed this problem by looping through each element of the array and writing them individually in to cells but this is far slower so I'd like to know if there is a better solution than that.
View 2 Replies
View Related
Feb 20, 2014
I need a formula that will calculate the percentage of the total duration between two dates (in dd.mm.yyyy hh:mm format) that a third, intervening date represents.
As an example:
a) 10th Jan 2014 09:00
b) 20th Jan 2014 09:00
c) 30th Jan 2014 09:00
In this case the answer sould be 50% because 'b' represents exactly half of the time elapsed between 'a' and 'c'.
Note: the formula needs to work effectively across the boundary between two consecutive years.
View 1 Replies
View Related
Jan 21, 2013
How do I create a Named Range that is dynamic (I think using the OFFSET function) that automatically expands and contracts as the rows and columns of my data changes?
Next step, I then want to use that Named Range as the source of the data for a PivotTable.
Yes, I know starting in Excel 2007 you can use Tables and don't need to create a dynamic named range. That is not an option at this time.
View 1 Replies
View Related
May 19, 2013
a VBA problem I have and I do not know how to solve. I use Excel 2003.
I have a workbook with a table; all cells except the table area are locked so at users cannot add any data. The table is on D2: H?. The workbook is password protected (users can only "Select unlocked cells", "Format cells" and "Sort"), and for that reason there is a "Add Rows" macro button so they can add more rows if necessary. It works well.
There is also a "Delete Rows" button, but it does not work as I want it to do. If the table is for example on D2:H4, and I select a cell on row #3 (ex. D3, or E3, or H3) and click on the button it deletes row #3, if I click on the button again it deletes row #4, and if I click on the button again it also deletes row #5 and so on, but it should not do it because all rows below row #4 are locked and should not be deleted.
I found on the Internet a code that I modified, but I do not get it to work. Below are the two macros.
My original macro (it deletes even locked rows):
Sub DeleteRows()
'
ActiveSheet.Unprotect Password:="123"
'
Selection.EntireRow.Delete
'
ActiveSheet.Protect Password:="123", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowSorting:=True
End Sub
The macro found and modified (the original rows are commented):
Sub DelRows_2()
Dim rngDelete As Range
Do While rngDelete Is Nothing
On Error Resume Next
[Code]....
View 2 Replies
View Related
Oct 4, 2013
Excel 2003
I have a table with a header row of scores 1-10 in B4:K4.
In column A I have a list of locations A5:A68. People in each location were asked to score an event between 1 and 10. The count of their scores is under B4:K4, eg Location 1, 3 people scored 1 (entered in B5), none scored 2 (C5), 6 scored 3 (D5) etc through to the score for 10 in K5.
What I am after is the average for each location so that in L5 I can say 'for all the respondents for Location 1, the average was:...
This may be a bit more complicated than it appears as presumably there will be a requirement to multiply the number of respondents by their scores and then ... ?
View 9 Replies
View Related
Feb 17, 2010
I have a pivot table based on a large dataset within Excel itself. The dataset looks as follows:
View 9 Replies
View Related
Dec 15, 2008
I have list of dates:
e.g: D/M/Y
1/03/1997
22/05/2005
13/09/1945
I want a new list that just shows the year and is formatted as a number
e.g:
1997
2005
1945
Is there a way of doing this without doing it manually, I have 20,000 observations.
View 9 Replies
View Related
Aug 7, 2009
The issue is i want years and months between two dates which are not in computer language. Date like 2008/12 and 2010/01. File is attached for you reference
View 2 Replies
View Related
