Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-25-2011, 10:17 AM
meninio's Avatar
meninio meninio is offline Formulate month in Excel 2010 Windows XP Formulate month in Excel 2010 Office 2010 32bit
Novice
Formulate month in Excel 2010
 
Join Date: Jan 2011
Location: Brasil
Posts: 15
meninio is on a distinguished road
Default Formulate month in Excel 2010


Guys, Good Afternoon.

I have start date 01/30/2009 (A2) and end date 29/01/2011 (B2). That totals 24 months. When I use the formulate month = month (B2-A2) appears 12. Someone could tell me why this?

Sorry for the mistakes, I am Brazilian and it's hard to get help at the forums of my country.

Thanks
Reply With Quote
  #2  
Old 01-25-2011, 11:13 AM
ConneXionLost's Avatar
ConneXionLost ConneXionLost is offline Formulate month in Excel 2010 Windows XP Formulate month in Excel 2010 Office 2003
Simulacrum
 
Join Date: Jan 2010
Location: Victoria, Canada
Posts: 86
ConneXionLost is on a distinguished road
Default

In your formula, you appear to be trying to subtract one date from another to get the number of days difference, then asking for the number of months those days would total.

The first part is okay, since your result of B2-A2 comes to 729 days. The problem is with your use of the MONTH function. The MONTH function will give you a month (as a number 1-12) if you give it a date. However, you are asking Excel to tell you what month is "729". Excel is converting the number 729 to a date, in this case it is 29 Dec 1901. The MONTH function then correctly provides you with the number 12 to represent December.

It might be better for you to use the DATEDIF function instead.

For your example, you might try this:

Code:
=DATEDIF(A2,B2,"m")&" months "&DATEDIF(A2,B2,"md")&" days"
Cheers,
Reply With Quote
  #3  
Old 01-25-2011, 11:36 AM
meninio's Avatar
meninio meninio is offline Formulate month in Excel 2010 Windows XP Formulate month in Excel 2010 Office 2010 32bit
Novice
Formulate month in Excel 2010
 
Join Date: Jan 2011
Location: Brasil
Posts: 15
meninio is on a distinguished road
Default

Sorry for my ignorance, but my knowledge is limited! How that would be the formulate?
Reply With Quote
  #4  
Old 01-25-2011, 11:47 AM
ConneXionLost's Avatar
ConneXionLost ConneXionLost is offline Formulate month in Excel 2010 Windows XP Formulate month in Excel 2010 Office 2003
Simulacrum
 
Join Date: Jan 2010
Location: Victoria, Canada
Posts: 86
ConneXionLost is on a distinguished road
Default

Have a look.
Attached Files
File Type: xls Using DATEDIF.xls (13.5 KB, 43 views)
Reply With Quote
  #5  
Old 01-25-2011, 12:13 PM
meninio's Avatar
meninio meninio is offline Formulate month in Excel 2010 Windows XP Formulate month in Excel 2010 Office 2010 32bit
Novice
Formulate month in Excel 2010
 
Join Date: Jan 2011
Location: Brasil
Posts: 15
meninio is on a distinguished road
Default

Now it worked, thanks for the help.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2010 view of cel missing isla Excel 2 09-27-2013 09:35 AM
Formulate month in Excel 2010 Excel 2010 Custom Ribbon judicial85 Excel 3 12-05-2010 07:22 PM
Get just grey box when opening Excel Document 2010 sebbero Excel 1 11-10-2010 04:24 AM
Can't see delegates calendar past next month. enviroko Outlook 3 08-08-2010 01:10 PM
Displaying work week in month view tupham Outlook 0 10-28-2008 02:55 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:05 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft