Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-17-2012, 12:47 PM
Lights Lights is offline Date Calculation Windows XP Date Calculation Office 2007
Novice
Date Calculation
 
Join Date: Apr 2012
Posts: 4
Lights is on a distinguished road
Default Date Calculation

I have two fields which I an concerned with, A1 and A2. In A1 I have a fixed date, we'll say 1/1/11. In A2 I want it to calculate the time that has passed since that date. SO if today was 2/1/11 in A2 it would say 1mos. I am targeting years and months and I'm not really concerned with days. Is this possible? Thanks!
Reply With Quote
  #2  
Old 04-17-2012, 01:04 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Date Calculation Windows 7 64bit Date Calculation Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Perhaps
Code:
=IF(DATEDIF(A1,A2,"y")=0,"",DATEDIF(A1,A2,"y")&" years ")&IF(DATEDIF(A1,A2,"ym")=0,"",
DATEDIF(A1,A2,"ym")&" months ")&DATEDIF(A1,A2,"md")&" days"
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 04-17-2012, 01:32 PM
Lights Lights is offline Date Calculation Windows XP Date Calculation Office 2007
Novice
Date Calculation
 
Join Date: Apr 2012
Posts: 4
Lights is on a distinguished road
Default

It says Circular Reference Warning and the result of the cell is 0
Reply With Quote
  #4  
Old 04-17-2012, 11:48 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Date Calculation Windows 7 64bit Date Calculation Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Ooops , sorry
Replace A2 with TODAY() in the above formula tHat should do the trick
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #5  
Old 04-18-2012, 12:11 AM
caholmes caholmes is offline Date Calculation Windows Vista Date Calculation Office 2007
Advanced Beginner
 
Join Date: Dec 2008
Location: Sydney, Australia
Posts: 54
caholmes is on a distinguished road
Default

Hi Lights,

I'm not a big fan of adding text to the formula result because it stops you using that answer in another formula. I would possibly use the following formula:

=(MONTH(TODAY())+YEAR(TODAY())*12)-(MONTH(A1)+YEAR(A1)*12)

This will give you an integer (whole number) as a result. After that I would use a custom format to add the text that you want into the same cell as the formula.



After you enter the formula:
  1. go into format cells (Ctrl + 1)
  2. select the number tab
  3. choose custom (bottom of the list)
  4. in the type box, type: 0" mos"
  5. click OK
This will display at least 1 number and add " mos" after it and it won't break any formulas.
Reply With Quote
  #6  
Old 04-18-2012, 04:31 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Date Calculation Windows 7 64bit Date Calculation Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Caholmes

your formula does not take the day into account but only months, therefore creating a result which might not be what the OP needs. Test it for March 31st and you will see that it returns 1 month, although it's less than one month. ( 19 days today)

If one is only concerned about the number of months
Code:
=DATEDIF(A1,today(),"m")
gives the number of entire months between two dates

But then again, as is common with dates, the OP did not specify what he wants exactly
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
calculation of start date ketanco Project 1 02-29-2012 07:01 AM
Date Calculation Formula calculation danbl Excel 8 02-23-2012 04:35 AM
Date Calculation Calculation within Cells manich1 Excel 2 12-07-2011 02:59 PM
Date Calculation Excel Calculation Problem UnholySmoke Excel 2 09-14-2011 08:15 AM
Date Calculation Help with 'hours worked' calculation... Snvlsfoal Excel 1 08-11-2011 05:54 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:29 AM.


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