Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    3 Star Lounger
    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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/month
    Last edited by sdckapr; 2011-05-24 at 20:11.

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    skipro (2011-05-25)

  4. #3
    3 Star Lounger
    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?

  5. #4
    WS Lounge VIP sdckapr's Avatar
    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?
    Datedif works on number of days. If you want to take differences in months you can do the math based on the days or let datedif do it. But perhaps I do not understand. Could you elaborate

    I am trying to digest your explanation regarding DATEDIF's potential inaccuracy
    It is not really the inaccuracy of DateDif but the fact that the number of days in a year can be either 365 or 366. A little more than 3/4 of the years have 365 days. And the number of days in a month can vary anywhere from 28-31. DateDif takes the number of days in the year and in the month into account, thus the number of days translated to months and years will depend on the starting date. For example if you have 31 days, if you start with a month that has 31 days, you have 0 mos and 31 days. But if you start with a month that has only 30 days you have 1 month and 1 day. If you have Feb, you either have 1 month and 1 day or 1 month and 2 days as the answer. Even though they are all different, none are inaccurate per se, it depends on the question you are asking....

    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

  6. #5
    3 Star Lounger
    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
    Attached Files Attached Files

  7. #6
    WS Lounge VIP sdckapr's Avatar
    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?
    Yes that is 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?
    It would not add any days or subtract as far as I understand (though maybe we are just thinking about it differently). Excel knows how many days are between Today and 12/12/2015. On the 12th of December of ANY year, the year will decrement. On the 12th of any month, the month with decrement. The days will just be the difference. When a month decrements, you will go to x months 0days to (x-1) months and either 30 days, 29 days, 28 days, or 27 days depending on the month.

    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?
    No it would not. All will be based on the start date being Jan 1, 1900, the first month has 31 days, then 29 days, 30 etc, and the first year is a leap year. This can be different than the values you get datedif with the actual date and today...

    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::
    Unfortunately I need help with your explanation. Could you elaborate with a few representative examples of dates (expiration and "current date") and what you need the results should be with those examples.

    Steve

  8. #7
    3 Star Lounger
    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.

  9. #8
    WS Lounge VIP sdckapr's Avatar
    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; 2011-05-26 at 15:15. Reason: Add PS and PPS

  10. The Following User Says Thank You to sdckapr For This Useful Post:

    skipro (2011-05-26)

  11. #9
    3 Star Lounger
    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

  12. #10
    WS Lounge VIP sdckapr's Avatar
    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

  13. #11
    3 Star Lounger
    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.

  14. #12
    WS Lounge VIP sdckapr's Avatar
    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

  15. #13
    3 Star Lounger
    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.
    Attached Files Attached Files

  16. #14
    WS Lounge VIP sdckapr's Avatar
    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

  17. The Following User Says Thank You to sdckapr For This Useful Post:

    skipro (2011-05-27)

  18. #15
    3 Star Lounger
    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?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •