Calculate number of months

Hello,

I have a field where I am trying to implement a calculation. I want it to 
take the date in a cell and subtract it from today's date to show me the 
total number of months between those two dates.  So right now, it looks like 
=(TODAY())-D3 and it returns 167 - the total number of days.  How do I make 
it show me months?

THANK YOU IN ADVANCE 


0
beverly1 (26)
12/8/2008 9:07:28 PM
excel 39879 articles. 2 followers. Follow

5 Replies
534 Views

Similar Articles

[PageSpeed] 17

=Month(Today())-Month(D3)

"TxWebDesigner" <beverly@beverlylanedesigns.com> schreef in bericht 
news:e3fR#jXWJHA.5032@TK2MSFTNGP05.phx.gbl...
> Hello,
>
> I have a field where I am trying to implement a calculation. I want it to 
> take the date in a cell and subtract it from today's date to show me the 
> total number of months between those two dates.  So right now, it looks 
> like =(TODAY())-D3 and it returns 167 - the total number of days.  How do 
> I make it show me months?
>
> THANK YOU IN ADVANCE
> 
0
jvw.merks (27)
12/8/2008 9:19:52 PM
=((YEAR(TODAY())*12)+MONTH(TODAY()))-((YEAR(D3)*12)+MONTH(D3))

Then Format->Cells->Number->Custom = 0 "months"

....should work for just about anything.
0
google5299 (29)
12/8/2008 9:32:23 PM
Does it cross over Year-to-year?
What about something like 11/1/2008 to 2/28/2009?

If so, you may want to have your formula be:

=3DMonth(Today())-Month(D3) + 12 * Year(Today())-Year(D3)


On Dec 8, 1:19=A0pm, "Just Merks" <jvw.me...@hccnet.nl> wrote:
> =3DMonth(Today())-Month(D3)
>
> "TxWebDesigner" <beve...@beverlylanedesigns.com> schreef in berichtnews:e=
3fR#jXWJHA.5032@TK2MSFTNGP05.phx.gbl...
>
> > Hello,
>
> > I have a field where I am trying to implement a calculation. I want it =
to
> > take the date in a cell and subtract it from today's date to show me th=
e
> > total number of months between those two dates. =A0So right now, it loo=
ks
> > like =3D(TODAY())-D3 and it returns 167 - the total number of days. =A0=
How do
> > I make it show me months?
>
> > THANK YOU IN ADVANCE

0
12/8/2008 9:34:31 PM
Try using the undocumented DATEDIF function...

=DATEDIF(D3,TODAY(),"m")

where the first argument's date must be an earlier (or equal) date than the 
second argument's date.

-- 
Rick (MVP - Excel)


"TxWebDesigner" <beverly@beverlylanedesigns.com> wrote in message 
news:e3fR%23jXWJHA.5032@TK2MSFTNGP05.phx.gbl...
> Hello,
>
> I have a field where I am trying to implement a calculation. I want it to 
> take the date in a cell and subtract it from today's date to show me the 
> total number of months between those two dates.  So right now, it looks 
> like =(TODAY())-D3 and it returns 167 - the total number of days.  How do 
> I make it show me months?
>
> THANK YOU IN ADVANCE
> 

0
12/9/2008 12:15:31 AM
Hi,

Since you are working with dates you can use

=DATEDIF(D3,NOW(),"m")

-- 
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"TxWebDesigner" wrote:

> Hello,
> 
> I have a field where I am trying to implement a calculation. I want it to 
> take the date in a cell and subtract it from today's date to show me the 
> total number of months between those two dates.  So right now, it looks like 
> =(TODAY())-D3 and it returns 167 - the total number of days.  How do I make 
> it show me months?
> 
> THANK YOU IN ADVANCE 
> 
> 
> 
0
12/9/2008 3:20:01 AM
Reply:

Similar Artilces:

adding months to an inputted date
I need a function that will take a date that a user has typed in a different cell and will then add two months to the date. For instance, if I type "2/12/05" in B1, then I want C2 to be: "4/12/05". Thank you for any help that you may be able to give. Logan =DATE(YEAR(B1),MONTH(B1)+2,DAY(B1)) however what do you want the date to be in C2 if B1 is 01/30/05? Regards, Peo Sjoblom "BLW" wrote: > I need a function that will take a date that a user has typed in a different > cell and will then add two months to the date. For instance, if I type >...

How to Replace Numbers with Phrases
-- Jerry ...

Convert date to months
I have a database that has multiple dates in it. What I am trying to do is in one field I have a date and I am want that date to convert to months in another field. Can anyone help me on this. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200707/1 are you trying to find months between dates? or convert 4 to April? Look at format function for ways to display months, datediff function for months difference.. "bohon79 via AccessMonster.com" <u35329@uwe> wrote in message news:74ac2f3f40ce9@uwe... >I have a database ...

HELP
I just wrote a check to the IRS, and in the Expense Catagory I indicated "Taxes : Federal Income Tax". Yet in the Income/Expense report, this payment is not included in my total federal taxes paid for this month. I should note that the check was written from an Investment Account. If I write the check from my checking account, the amount correctly shows up in the Income/Expense report. Any ideas as to why it works differently, depending on which account I pay the tax from? In microsoft.public.money, Tom wrote: >I just wrote a check to the IRS, and in the Expense Cat...

Calculating values for empty cells.
Hello. I have a very simple problem that I cannot find the answer to. I have data in two columns, some of the data in one of the columns is missing and I want to automatically extrapolate what the data should be based on the trend. How can I get Excel to fill in empty values without overwriting the known values. Below is a sample of my data. 1500 1600 1700 1800 4000 1887 5700 1900 5500 1910 7300 1912 8100 1920 8800 1926 10100 1930 11900 1936 12200 1938 -- Ryan Taylor rtaylor@stgeorgeconsulting.com Not sure what yo...

hours/month excluding weekends (10hr)
I need to calculate weekday hours in particular month; excludin holidays and weekends, based on 10 hr work days -- Message posted from http://www.ExcelForum.com use: Start date of month in A1 End Date of month in A2 # of Holidays (not including weekends) in cell A3 cell A4: =networkdays(A1,A2,A3)*10 HTH, ryanb. "cwren" <cwren.yi2om@excelforum-nospam.com> wrote in message news:cwren.yi2om@excelforum-nospam.com... > I need to calculate weekday hours in particular month; excluding > holidays and weekends, based on 10 hr work days. > > > --- > Message pos...

Custom header numbering
Hi all, I have a question about formatting Header numbering throughout a new document (Word 2007). I'm trying to do the following: Heading 1 1.0 Heading 2 1.1 Heading 2 1.1.0 Heading 3 1.1.1 Heading 3 Heading 1 2.0 Heading 2 2.1 Heading 2 2.1.0 Heading 3 ... So it's an outline numbered / multilevel list, where Heading 1 has no numbering, and Heading 2 restarts numbering after a Heading 1 is used. I haven't been able to figure this one out for myselve. Can anyone help me on this one? Regards, Bert -- Neron Follow the instructions at ...

How to assign unique number to column duplicates?
Hi All, I need to assign a unique number to a set of duplicates all in one column in Excel 2007. so columnA will has about 9000 numbers, some of them unique, and others are duplicates of 2-4 approx. I used to conditional formatting to show which are duplicates, but need to be able to assign a unique number to each set duplicates, that will be in sequential order... e.g. ColumnA ColumnB(unique ID) 01233 0001 01233 0001 01234 - 01255 0002 01255 0002 etc.... Any ideas please? I don't know how to do programming, just form...

sorting numbers #5
Hi were have problems in sorting out our inventory item numbers. We have over 15,000 item numbers that can't be changed or zeros deleted. The problem is when there are zeros in the item number. Excell sort CA0002 CA00020 CA0021 CA02 CA021 CA02C Needs to be CA0002 CA02 CA02C CA00020 CA0021 CA021 Thanks Darkjedi -- darkjedi ------------------------------------------------------------------------ darkjedi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29153 View this thread: http://www.excelforum.com/showthread.php?threadid=488748 Since I didn't re...

Get Start date of Week number and Year
I’d like to build the following expression in my query GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate])) So if EnteredDate = 11/3/2009 the function would return 11/1/2009 But GetStartWeekNumber does not exist as an Access Built-In Function. Is there another way to do this as an expression in a query? I’m not familiar with creating my own functions. Thanks. That would depend on how you define the start of the week... One option would be to get the day-of-week number of the date (in my system/setup, Monday is day 2), then subtract one less than that...

Month view: scroll to see more?
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC Email Client: Exchange Hello-- At MIT we have recently migrated to Exchange, which seems to be a setback in myriad ways. Does anyone know how to scroll in calendar Month View to see all items on a single day? I need to stay in Month View but have the ability to see all items on each day. I don't want to move to day view. Thanks! ...

I am having trouble keeping numbers formatted in a Pivot Table
I have a pivot table that won't stay formatted. The data is formatted as currency in the worksheet that the Pivot Table is drawing off of. It shows up as a general number in the Pivot. I've tried formatting it in the Pivot but it doesn't stay when I refresh the data. "Preserve formatting" is checked on the Pivot table, so it SHOULD be preserving any formatting I apply to the pivot table, but that doesn't seem to be happening. I know I could record a macro to format this, but it is happening on multiple Pivot tables in the same workbook and that would be a pain ...

Increasing Month only in formula
Kindly i need help on below: i have a formula as "=Jan!G$2" in Cell "A1", i'm looking to have "=Feb!G$2" in Cell "A2" and "=Mar!G$2" in Cell "A3" and so on by simple dragging. is that possible? On Thu, 11 Feb 2010 12:53:01 -0800, Malla <Malla@discussions.microsoft.com> wrote: >Kindly i need help on below: > >i have a formula as "=Jan!G$2" in Cell "A1", i'm looking to have "=Feb!G$2" >in Cell "A2" and "=Mar!G$2" in Cell "A3" and so o...

records in current month
Hi I want to use a query that returns records in the current month from a date field, but cant work the query out - anyone help please Thanks Alec Set the criteria under the date field to: Between Date()-Day(Date())+1 AND DateSerial(Year(Date()),Month(Date())+1,0) -- Duane Hookom Microsoft Access MVP "alecgreen" wrote: > Hi > > I want to use a query that returns records in the current month from a > date field, but cant work the query out - anyone help please > > Thanks > > Alec > > ...

False number of unread messages
For the last week or so my folders list shows one unread message in my Inbox, but there are none. Anyone know how I can correct this please? "Ayrhead" <Ayrhead@discussions.com> wrote in message news:61EED10D-B8A6-4293-A60D-540E9E90C446@microsoft.com... > For the last week or so my folders list shows one unread message in my > Inbox, > but there are none. Anyone know how I can correct this please? View menu>current view>disable group messages by conversation should reveal the missing message. -- Regards Steve. MS-MVP. MAIL. [DTS] UK. ht...

Monthly calendar view -- Saturday and Sunday
I am using a wide-screen monitor and would like to have the monthly view show all seven days across instead of Saturday and Sunday sharing one box. Is there a way to do that? Charles Please ignore that request. I just figured it out!!! "Charles Lewis" <clewis50@yahoo.com> wrote in message news:Hrwvf.37355$9G.34667@dukeread10... >I am using a wide-screen monitor and would like to have the monthly view >show all seven days across instead of Saturday and Sunday sharing one box. >Is there a way to do that? > > Charles > > ...

Re: limit numbers of connections to one server
hi there :-) my pop3 server is limited to 10 query per sec. and per ip. i have abou 16 mail-accounts on this server. the last 6 allways error with timeou or something. is there a way to limit the number of connections to one server i outlook 2002 on windows xp pro? thanks a lo - jazzy_ ----------------------------------------------------------------------- Posted via http://www.mcse.m ----------------------------------------------------------------------- View this thread: http://www.mcse.ms/message674073.htm Set your send/receive settings to consecutive mail checks, rather than conc...

Summing with a range of number
Hey Everyone, I have a question about summing via ranges. I am trying to write macro and have hit a bump. Here is the scenario. I have a 2 columns of data (A&B). Column B i sorted in ascending order. Now I have ranges of data in column A that need to sum. Lets say in column B I need all numbers from 100 to 300 From 100 to 300 I need the totals in column A to sum at the last numbe that is less than 300 but greater than 100. I would like the sum to b in colum C for the totals of column A within the ranges of column B. hope that makes sense. If not ask for clarification or I will try an...

Add times and genral numbers
I have a spread sheet that calculates a score based on the time to finish an event PLUS points scored during the event. Currently I enter the times as seconds (general number Eg 2 minutes = 120. This allows for easy adding of time and points. Each second of event time counts as 1 point. Eg 2 Minutes 30 Seconds is 150 points PLUS 50 points scored during event for a total of 200 points. I want to be able to add times in the correct format. Eg 2:30 for 2 minutes 30 seconds instead of using 150 seconds (actually I would prefer 2.30 for speed) How can I do this to allow for adding a time of 2:3...

How can I keep my numbering system consecutive when I sort?
I have created a spread sheet with a numbering system. I would like it to be permanently consecutive when I do a sort...is there a way to create a permanent row that is not affected when I sort the rest of the document? One way: Say your row #1 starts on Row5. In A5 enter: =ROWS($1:1) And copy down as needed. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "duchess_2" <duchess_2@discussions.microsoft.com> wrote in message news:290570A8-5847-40...

Caption Numbering connected with various heading styles
I am working on a document with many (100+) figures with captions that are located in various chapters, sections and subsections. I don't have a problem numbering the caption automatically with the appropriate heading level but when I adjust the caption heading level in the caption dialog box (e.g. from H3 to H4) all of my previously created H3 headings are changed to H4 level headings. In shorter documents I have simply gone back and adjusted the \s 4 to \s 3 (or as appropriate) after completing the document but that is getting rather tedious with the large number of figures...

Calculation Error
I'm using Access 2002 and the file format for the DB is 2000. I have a calculation that works fine for me but when another user logs on and runs the same report she receives an #Error instead of the number. I looked at the references and noticed the Microsoft Access Object Library I am using is 11.0 and the other user has 10.0. Could this be the problem. If so, how do I fix this since she doesn't have 11.0 in her list. If it's not this what other possibilities could there be? Sorry, I made a typo. It's 2003, not 2002. I also fiddled with the calculation. ...

Loss calculations
Hi all, I've encountered a problem which I can't solve in Excel. Can someone please help with this? I have 2 columns: A and B. A contains Year information, while column B contains Losses, e.g.: Year Losses 1980 45 1980 500 1980 6,500 1981 1,002 1981 2,150,000 1981 6,500,000 1982 1,350,000 1982 490,650 1982 950,000 1983 56,000 1983 656,580 Now here's what I need - as you can see, the same year can appear more than once. This means that more loss driving events happen in that year. So, I need to look in every year in top to bottom or...

International Phone Numbers #2
Just a suggestion, but I think that there should be some sort of option to set up a customer as domestic or international, and for the Customer/Vendor card to reflect this. What I mean is that when you have an international phone number, the phone number fields should change to be able to accept international numbers, with a space for the country code. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the butto...

Addition problem, number always 2 cents off...Help
I have a little problem with my SUMs I have 200 to 300 rows of A * B = C Then I tell it to do a =sum(d1:d300) All the cells are formated to currency, two decimal places. when I add up the numbers with an adding machine I'm always 2 - 22 cents off. (2 cents on this sheet, 22 cents on another sheet....) Can anyone help Thank you WTG The issue is very likely to be rounding - that the product of each multiplication has fractional cents that Excel considers in the SUM() function, even though you don't see them fractional cents on screen You can either modify your multiplication f...