Microsoft Office Forums Date Calculation
 Register FAQ Search Today's Posts Mark Forums Read

#1
04-17-2012, 12:47 PM
 Lights Windows XP Office 2007 Novice Join Date: Apr 2012 Posts: 4
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!
#2
04-17-2012, 01:04 PM
 Pecoflyer Windows 7 64bit Office 2010 64bit Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,436

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"```
#3
04-17-2012, 01:32 PM
 Lights Windows XP Office 2007 Novice Join Date: Apr 2012 Posts: 4

It says Circular Reference Warning and the result of the cell is 0
#4
04-17-2012, 11:48 PM
 Pecoflyer Windows 7 64bit Office 2010 64bit Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,436

Ooops , sorry
Replace A2 with TODAY() in the above formula tHat should do the trick
#5
04-18-2012, 12:11 AM
 caholmes Windows Vista Office 2007 Advanced Beginner Join Date: Dec 2008 Location: Sydney, Australia Posts: 54

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.
#6
04-18-2012, 04:31 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,436

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

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post ketanco Project 1 02-29-2012 07:01 AM danbl Excel 8 02-23-2012 04:35 AM manich1 Excel 2 12-07-2011 02:59 PM UnholySmoke Excel 2 09-14-2011 08:15 AM Snvlsfoal Excel 1 08-11-2011 05:54 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 09:40 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top