Microsoft Office Forums Date Calculation

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,397
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
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"
__________________
Please note I am not active on this forum anymore
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,397
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Ooops , sorry
Replace A2 with TODAY() in the above formula tHat should do the trick
__________________
Please note I am not active on this forum anymore
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,397
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
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
__________________
Please note I am not active on this forum anymore
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


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


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