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

## Excel 2003 :: Pulling Data From Amortization Schedule To Debt List

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?

## Get Excel To Calculate Elapsed Time Between Two Dates?

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.

## Excel 2003 :: Formula To Determine If Someone Is 21 Years Old Or Not

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.

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

## Excel 2003 :: Copy And Move Files To Create Backups For 7 Or 8 Years

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.

## Excel 2003 :: Pivot Table With 1 Row And 1 Column Field

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

## Excel 2010 :: Grouping Dates Across 2 Different Years

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?

## Excel 2010 :: Time Between Dates In Years And Quarters (.25)

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.

## Add 65 Years To Column Of Birth Dates And Result Must Be In Yyyy/mm/dd Format?

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?

## Using If Function In An Amortization Table

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.

## Amortization Table Formula

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.

## Amortization Table For X Year Mortgage

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.

## Delete Fields That Correspond To Certain Months In Amortization Table

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

## Excel 2003 :: Email Triggers From Dates

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‎

## Excel 2010 :: Dates As Headers In Table Not Recognized As Dates For Charting?

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.

## Excel 2003 :: Compare Two Dates With Dynamic Operator

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

## Excel 2003 :: Formula Using Dates To Calculate Amounts?

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.

## Excel 2003 :: Get Dates To Remain In Consistent Format

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

## Calculate Time Elapsed Between Start And Finish Dates

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?

## Average Elapsed Time With Conditions Using Dates & Numbers

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

## Produce A Report That Details Elapsed Time Between Two Dates And Times

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?

## Excel 2003 :: Array Dates Transposed Results In Text And Wrong Format?

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.

## Formula To Return Percentage Of Duration Elapsed Between Two Dates A Third Date Represents?

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.

## Excel 2003 :: Dynamic Range For Pivot Table

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.

## Excel 2003 :: Macro To Delete Rows In A Table

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()
'
'
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]....

## Excel 2003 :: Index And Match - Table With Header Row Of Scores

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

## Unnecessary Blank Rows In Excel 2003 Pivot Table

Feb 17, 2010

I have a pivot table based on a large dataset within Excel itself. The dataset looks as follows:

## Changing Dates To Years

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.

## Years And Months Between Two Dates

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