Results 1 to 15 of 25
Thread: Get yr, mos, days returns

20110524, 19:30 #1
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
Get yr, mos, days returns
How do I get Yr, mos, day return from a cell with only yrs or mos or days?
Ex:
A1=894 days or 29 mos or 2.45 yrs
I want B1 to return 2 yrs, 5 mos, 10 days
I realize I need more decimal points [example] in days and months or I will only get an approximate. I just need the formula to get me started.
I cannot use DATEDIF [ym, md], because A1 is not a period between 2 dates.
Formula for A1 is [=E23+E23*(5/12)]
Thanks

20110524, 20:03 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
This calculates: 2 yrs, 5 mos, 12 days
=DATEDIF(0,A1,"y")&" yrs, "&DATEDIF(0,A1,"ym")&" mos, "&DATEDIF(0,A1,"md")&" days"
It will depend on what date you want to start from the number of years, months, and especially days...
Steve
PS The above assumes A1 days from Jan 1, 1900 and calculates (incorrectly) that 1900 is a leap year (due to a flaw in Lotus123 logic that XL kept for compatibility)
on the other hand this:
=INT(A1/365)&" yrs, "&INT(MOD(A1,365)/30)&" mos, "&MOD(MOD(A1,365),30)&" days"
calculates: 2 yrs, 5 mos, 18 days
it assumes exactly 365 days/year and exactly 30 days/monthLast edited by sdckapr; 20110524 at 20:11.

The Following User Says Thank You to sdckapr For This Useful Post:
skipro (20110525)

20110525, 12:32 #3
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
Steve,
Thanks.
In the DATEDIF example you gave, Nothing [a "0"] can mean so much. Using "0" for the first date is so simple it is eloquent.
Does this only work with # of days, as compared to # months or years? Is there a direct way to work with # of month/yrs without determining the # of days?
I am trying to digest your explanation regarding DATEDIF's potential inaccuracy.
Is DATEDIF accurate when using 2 reasonably current dates?
Which is generally more accurate, using DATEDIF between two dates [Ex: #1] or using DATEDIF with a number of days [Ex: #2]?
Ex: #1
=DATEDIF(TODAY(),A1,"Y")&" Years, "&DATEDIF(TODAY(),A1),"YM")&" Months, "&DATEDIF(TODAY(),A1,"md")&" Days"
where A1 is a date in the future
[versus]
Ex: #2
=DATEDIF(0,A1,"y")&" yrs, "&DATEDIF(0,A1,"ym")&" mos, "&DATEDIF(0,A1,"md")&" days"
where A1 = 673 days [theoretically the same as above but calculated in a more round about manner]
Also:
Is DATEDIF more accurate than the INT(MOD) example?
Is there a way to compensate for these issues to arrive at an accurate return? Can inaccuracies be predicted/calculated?

20110525, 13:42 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Does this only work with # of days, as compared to # months or years? Is there a direct way to work with # of month/yrs without determining the # of days?
I am trying to digest your explanation regarding DATEDIF's potential inaccuracy
The Int/Mod examples does not take date into account at all, just assumes 365 days/year and 30 days/month so is consistent. It does not matter the year or current date, 31 days means 1 month and 1 day. 60 days = 2 months, etc. But it has its own problems. from 359  366 days you will get: 11 months + 29 days, 12 months, 12 mos+1 day, 12 mos+2 days, 12 mos+3 days, 12 mos+4 days, 1 year, 1 year+0 mos +1 day...
So again, it depends on what you want. For the numbers 1  370 do you want the Yr/mos/days to always be the same, no matter the Current date. Could you give us some sample numbers of when you want the months to change in those dates? Which dates are x Mos+0 days, and which is 1 yr+0 Mos+0 days? Do you want Leap years to be taken into account?
Steve

20110525, 15:55 #5
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
Steve,
Attached is sample.
I am seeking the most accurate results taking into consideration all variations in # of days in each month and leap years.
If the return is 2 yrs, 5 mos, 3 days, I want it to be accurate for its intended purpose.
A2 is "DATEDIF today()A1" to give me the Yrs, mos, days until A1
If I understand you correctly, B2 is accurate and takes all above variations into consideration. Is this correct?
I expect A2 would decrease 1 day daily. Would it also update according to the # of days in the current month and if in a leap year? In other words, would it add a few days in Feb or subtract one in a leap year and then readd the next year?
A5 is "DATEDIF(0,A4"
Does this also take into consideration the variations list above? Is it accurate?
Would A5 update according to the # of days in the current month and if in a leap year?
Need help with this::
A7 is an expiration date. A8 is the time remaing. I need to add 5 months per year on Dec 1 as long as time remains. The 5 months get added only if there is a remaining Dec 1. If the time expires prior to Dec 1, no 5 months would be added for that year. With the addition of these 5 month periods, additional/new Dec 1 may be added and they need to be included.
I need A8 to reflect this newly calculated and current remaining time.
Thanks

20110525, 18:56 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
If I understand you correctly, B2 is accurate and takes all above variations into consideration. Is this correct?
I expect A2 would decrease 1 day daily. Would it also update according to the # of days in the current month and if in a leap year? In other words, would it add a few days in Feb or subtract one in a leap year and then readd the next year?
Does this also take into consideration the variations list above? Is it accurate? Would A5 update according to the # of days in the current month and if in a leap year?
If you want to count down from a particular date to today, use the A2 formula. This will be accurate for that.
Need help with this::
Steve

20110525, 22:57 #7
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
Steve,
I want to predict/calculate the final expiration date of a contract [A2] starting with the original exp date [A1] where 5 months is added each year on Dec 1 until the contract expires[see explanation below]. If it expires prior to Dec 1, no time is added for that year.
A1 = The original expiration date is 10/12/2011
A2 = the recalculated expiration date
In this contract, the time between Dec 1 and May 1 [5 months or corresponding # of days] is not deducted and therefore the results are that every Dec 1, this time would be credited to the current expiration date, but only if Dec 1 is reached that year. If the date expires prior to Dec 1, no time is added for that year [the last year].
Ex:
Start with exp date = 10/12/2013
On Dec 1, 2011 add 5 months/corresponding # days to A1 [10/12/2013]
10/12/2013 + 5 months = 3/12/2014
temp new exp date = approx 3/12/2014
On Dec 1, 2012 add 5 months/corresponding # days to A1
temp new exp date = approx 8/12/2013
On Dec 1, 2013 add 5 months/corresponding # days to A1
temp new exp date = approx 1/12/2014
On Dec 1, 2014 do not add 5 months to A1 because it expired on 1/12/2014
A2 = 1/12/2014
This is approx as I did not factor the actual # days
Of course, I would like the return to be accurate, not approximate.
I would like to have a formula that would be flexible so I can factor in any time frame.
Any guidance is appreciated.
I hope this explains clearly want I am trying to do.

20110526, 15:06 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Try this in A2:
=DATE(YEAR(A1),MONTH(A1)+(INT((DATEDIF(DATE(YEAR(T ODAY())+(TODAY()>DATE(YEAR(TODAY()),12,1)),12,1),A 1,"m")+5)/7)+2)*5,DAY(A1))
It is based on A1 being the original "expiration date" and if the date is earlier than 12/1 using 12/1 of the current year, or if the date is later using 12/1 of the next year.
Steve
PS I think your logic is a bit off in your example, you add 5 months onto 3/12/2014 and get 8/12/2013 instead of 8/12/2014, so your ending values are all off a bit...
PPS if you don't want to use the current date [today()] you could define the year for the first Dec 1:
=DATE(YEAR(A1),MONTH(A1)+(INT((DATEDIF(DATE(2011,12,1),A1,"m")+5)/7)+2)*5,DAY(A1))
Or put it into cell (eg A2):
=DATE(YEAR(A1),MONTH(A1)+(INT((DATEDIF(DATE(A2,12,1),A1,"m")+5)/7)+2)*5,DAY(A1))Last edited by sdckapr; 20110526 at 15:15. Reason: Add PS and PPS

The Following User Says Thank You to sdckapr For This Useful Post:
skipro (20110526)

20110526, 16:56 #9
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
Steve,
Thanks.
When I use your formula, I return 11/12/2015 in A2. When I figure it out on paper, I get 6/12/2015.
You add 1 more 5 months then I do, see below.
You are correct, I was careless in my last post when I did this calculation. I think I got it right this time.
Start with exp date = 10/12/2013
On Dec 1, 2011 add 5 months
new exp date = 3/12/2014
Dec 1, 2012 add 5 months to A1
new exp date = 8/12/2014
Dec 1, 2013 add 5 months to A1
new exp date = 1/12/2015
Dec 1, 2014 add 5 months
new exp date = 6/12/2015
Dec 1, 2015 already expired
projected exp date = 6/12/15, not 11/12/15

20110526, 18:50 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
I was a little confused on logic. I got the Dec 1, 2014 add 5 months , new exp date = 6/12/2015 and not expired and then (incorrectly) went to
Dec 1, 2015 add 5 months , new exp date = 11/12/2015 Expired at 11/12/2015, not noticing that Dec 1, 2015 is > 6/12/2015. This makes more sense, since I was wondering why I needed to add 2...
The change is easy, add only 1, not 2 in one of the 3 earlier formulas:
=DATE(YEAR(A1),MONTH(A1)+(INT((DATEDIF(DATE(YEAR(T ODAY())+(TODAY()>DATE(YEAR(TODAY()),12,1)),12,1),A 1,"m")+5)/7)+1)*5,DAY(A1))
or
=DATE(YEAR(A1),MONTH(A1)+(INT((DATEDIF(DATE(2011,12,1),A1,"m")+5)/7)+1)*5,DAY(A1))
Or put it into cell (eg A2):
=DATE(YEAR(A1),MONTH(A1)+(INT((DATEDIF(DATE(A2,12,1),A1,"m")+5)/7)+1)*5,DAY(A1))
Steve

20110526, 23:27 #11
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
Steve.
With starting date 10/12/13, your formula returns 6/12/15, which is correct.
If I add 1 mos from start date and use 11/12/13, your formula returns 12/12/15. It should be 7/12/15, one mos later than above. Again 1 too many 5 mos additions.

20110527, 08:42 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
The change is easy, delete the "+5". This offsets the next expiration date to be with next year rather than the current:
=DATE(YEAR(A1),MONTH(A1)+(INT(DATEDIF(DATE(YEAR(TO DAY())+(TODAY()>DATE(YEAR(TODAY()),12,1)),12,1),A 1,"m")/7)+1)*5,DAY(A1))
or
=DATE(YEAR(A1),MONTH(A1)+(INT(DATEDIF(DATE(2011,12,1),A1,"m")/7)+1)*5,DAY(A1))
Or put it into cell (eg A2):
=DATE(YEAR(A1),MONTH(A1)+(INT(DATEDIF(DATE(A2,12,1),A1,"m")/7)+1)*5,DAY(A1))
Let me know if you find any other disparate dates with this logic...
Steve

20110527, 14:15 #13
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
I am not sure if you are suggesting the last formula, removing +5, was the solution to all dates or just the one with the error, 11/13.
Both formulas are correct sometime, incorrect others, sometimes both give correct formula.
see attached sample.
We need one that looks at each subsequent exp date and see if it is before or after the next possible corresponding update date[Dec 1]. If it is before, then do not add, if it is after, then add.

20110527, 15:32 #14
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
The new calc (w/o the +5) is meant to work with all of them. I seem to be missing the logic...
Look at the first one you claim is incorrect. Original date 1/1/2013. My calc is 11/1/2013, your claim is 4/1/2014
It starts at 1/1/2013
On 12/1/2011 it goes to 6/1/2013
On 12/1/2012 it goes to 11/1/2013
So it is expired BEFORE 12/1/2013
Another exp starts at 4/1/2013. You claim the new exp is 12/1/2014
On 12/1/2011 it goes to 9/1/2013
On 12/1/2012 it goes to 2/1/2014
On 12/1/2013 it goes to 7/1/2014
So it is expired BEFORE 12/1/2014...
I haven't looked at all the examples, but this is the type of thing I keep getting. So perhaps it is a matter of me not completely understanding the logic. Could you elaborate?
Steve

The Following User Says Thank You to sdckapr For This Useful Post:
skipro (20110527)

20110527, 18:01 #15
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
Steve,
You are correct, my mistakes. Sorry to bother you unnecessarily.
How would I do this if I was working with days not months, say 153 days?