Mark East

The UK's most regular instructor-led training courses.
Training information: visual+basic+for+excel+courses+london · Excel courses london · Microsoft Excel Training London
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · Medium Priority · Version 2003

Mark East

Selina has attended:
Excel Intermediate course

by - delegate Selina [2 posts] (2008 Jun 10 Tue, 17:03) Reply

When calculating the difference between two dates (possibly over more than a year), how do I show the result as a number of Months? Also can this be done as a formula to include today's date as the latter of the two dates?

Version	Date	Location	Places available	Book	Next place rate:
Excel VBA 2 day course
Version	Date	Location	Places available	Book	Card	Invoice
2003	2009 Jan 22 Thu + 23 Fri	Southwark	0	FULL
2003	2009 Jan 29 Thu + 30 Fri	Bayswater	4	book now	£475	£485
2007	2009 Feb 5 Thu + 6 Fri	Bloomsbury	5	book now	£485	£490
2003	2009 Feb 19 Thu + 20 Fri	Southwark	4	book now	£475	£485
2003	2009 Feb 26 Thu + 27 Fri	Bayswater	8	book now	£335	£365
2003	2009 Mar 12 Thu + 13 Fri	Bloomsbury	7	book now	£345	£399
Full Schedule: See all 43 Excel VBA course dates. Bookings currently available until 18th March 2010.

RE: Mark East

by - trainer David silver contributer [79 posts] (2008 Jun 12 Thu, 11:42) Reply

Hi Salina

If cell A1 is date from last year and cell A2 is current date you would do the formula as follows:
In cell A3 you would do a subtraction of the 2 cells and then format the cell as a number. This would give you number of days so we could make it a monthly figure by dividing by 30. Your formula would look like this:
=SUM(B1-A1)/30
Ensure that you have formated the cell as a number.

This cell would round at the moment and for your purposes you would want excel to always round down and never up. So you will need to do a further formula in cell D1 to force a round down. our formula would look like this =ROUNDDOWN(C1,0)

Hope that helps.
David

· MS Excel: Excel At Making The Most Of Your Christmas
· Microsoft Office and the Small Business: Part 1 Excel
· Why Excel Training (Sometimes) Doesn't Work and What You Can Do About It
· Advanced Excel Training - takes you to the next level
· Identifying Suitable Candidates For VBA For Excel Training

Please browse our web site to find out more about
visual+basic+for+excel+courses+london and other Microsoft training courses.

Excel tip:

Fill formulae across a sheet

To copy a formula down a spreadsheet where there is data underneath, to the left or to the right of the formula, double-click on the fill handle. The fill handle is the little black cross that appears in the bottom right-hand corner of the formula cell. Unfortunately, no similar facility exists to copy formulae across the sheet.

One reasonably quick way to copy an existing formula across a sheet is to select the formula and the cells on the right to which you want to copy it. Then press Ctrl+R to copy the formula across the selected range, or, if you are menu-minded, use the Edit|Fill|Right command.

View all Excel hints and tips

Rate this page: