1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2000

Discussion in 'Business Applications' started by Erm, Oct 16, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    Please help!

    I am subtracting one date from another and need to get the difference in a number of years months and days...I have a mental block and can't figure this out.

    Is it possible? Can anyone help?
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    You're in luck! 2K is the only version AFAIK that covers DATEDIF in Help.

    =DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")&" months "&DATEDIF(A1,B1,"md")&" days."

    Rgds,
    Andy
     
  3. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    I also need to add the differences between five sets of start and end dates and calculate the total difference in years, months, days. Here is the formula I entered (each of the cell references is a start and end date...does the order matter?)...I get #VALUE! in the cell.

    =(DATEDIF("D4","E4","Y"))+(DATEDIF("G4","H4","Y"))+(DATEDIF("J4","K4","Y"))+(DATEDIF("M4","N4","Y"))+(DATEDIF("P4","Q4","Y"))

    Any ideas?
     
  4. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    sorry...formula is now:
    =(DATEDIF(D4,E4,"Y"))+(DATEDIF(G4,H4,"Y"))+(DATEDIF(J4,K4,"Y"))+(DATEDIF(M4,N4,"Y"))+(DATEDIF(P4,Q4,"Y"))

    This produces a value which is incorrect, I think its the formatting on the cell...what should the format of the result cell be? It may not be this?!
     
  5. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    I have now done the full formula to see what happens...the formula is:
    =((DATEDIF(D4,E4,"Y"))+(DATEDIF(G4,H4,"Y"))+(DATEDIF(J4,K4,"Y"))+(DATEDIF(M4,N4,"Y"))+(DATEDIF(P4,Q4,"Y")))&" years "&((DATEDIF(D4,E4,"YM"))+(DATEDIF(G4,H4,"YM"))+(DATEDIF(J4,K4,"YM"))+(DATEDIF(M4,N4,"YM"))+(DATEDIF(P4,Q4,"YM")))&" months "&((DATEDIF(D4,E4,"MD"))+(DATEDIF(G4,H4,"MD"))+(DATEDIF(J4,K4,"MD"))+(DATEDIF(M4,N4,"MD"))+(DATEDIF(P4,Q4,"MD")))&" days"

    I get the answer 12 years 29 months 69 days

    I'm a little confused...I had calculated the difference as 14.62 years before...which if you work it out this is what 12 years 29 months 69 days is?!!!???!!! What on earth have I done?
     
  6. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    I didn't think I would get chance to have another look at it today, but when I did I cleared all the date cells and re-entered dates, this seems to have worked except for some examples when I get something like this...

    5 years 12 months 7 days...Shouldn't it be 6 years 0 months 7 days?
    Or even 5 years 16 months 29 days???!!!

    Is this to do with a default 28/30 day month? If it is, can this be changed???

    Or is it to do with adding the diffs together in the same formula? I think this is the problem actually...? I'll have another fiddle...
     
  7. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    I can't solve this problem. Can anyone help?
     
  8. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Hi Debs.

    Let's say you have 5 time periods, all are (a) various years (b) various months (c) 300 days.

    Add them all, ignoring years and months that's 1500 days. How many years is that? More than 4 (approx 4.10958904109589). How many months is 0.1? More than a month, but how long is a month? 365.25/12? (=30.4375)?

    I put our birthdates in A1:A5, and =TODAY() in B1:B5. Then
    =YEARFRAC(A1,B1)
    in C1, then copied down to C5, then
    =SUM(C1:C5) in C6.

    Result = 141.952777777778, which is probably correct in terms of rounding days to months and days to months to years.

    You can use =INT(C6) to extract years, all that's left is to convert the fractional year to months and days, depending on how you want to do it. You might want to look at =MOD.

    HTH,
    Andy
     
  9. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    I ended up with

    =INT(SUM(C1:C5))&" years "&INT((SUM(C1:C5)-INT(SUM(C1:C5)))/(1/12))&" months "&((SUM(C1:C5)-INT(SUM(C1:C5)))-(INT((SUM(C1:C5)-INT(SUM(C1:C5)))/(1/12))*(1/12)))/(1/365.25)&" days."

    You might want to throw in a rounding for the days bit.

    HTH,
    Andy
     
  10. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    FYI: my attempt

    I totalled up all the date differences in one TOTAL cell with the units of DAYS. Do it as you will. I used a =DATEDIF(A2,B2,"D") and then summed them all, but you could do it various ways. Using this method you can then add to the array and always have one cell of TOTAL DAYS to use in your formula.

    In my example I had my total in cell H8 with - 3184 (days)

    Then I used the formula:
    =INT(H8/365) & " YEARS " & INT(MOD(H8,365)/30) & " MONTHS " & MOD(MOD(H8,365),30) & " DAYS "

    That will take 3184 days and note and remove (8) 365 day years and then (8) 30 day months from what was left and then note the 24 days remaining.

    - C
     
  11. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    I saw an aberration. :eek:

    My formula would not correctly interpret 729 days - one day shy of two years. That traced back to my 30 factor.


    I changed that to 30.4 .... (365/12). I don't know what might be most accurate considering leap years and all. But that seemed to kick out the accurate answer.

    I'm not sure you're ever totally correct.

    4/02/02 to 5/02/03 is a year and a month.
    4/02/03 to 3/02/03 is 11 months

    one could view that total as 24 months - but the actual total of days is one day shy. My formula reports it that way:

    1 year 11 months 29 days.

    I guess it's all in what you need to describe in your formula.

    - C ;)
     
  12. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    That should be sufficient as far as I am concerned, I will have to speak to the user! Thanks everyone for your help you have all been great!
     
  13. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    This will be sufficient but it would be "preferable" if it could calculate the ACTUAL date dif, looking at the number of days in the various months, even accounting for leap years. This would obviously be better than using an approximate figure to represent a month ie 30.4 days as a month.

    Is there any possibility that this can be done?
     
  14. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    Probably not. At least not without a whole lot of massaging.

    If I understand... "I also need to add the differences between five sets of start and end dates and calculate the total difference in years, months, days." then you are totaling up spans of dates across the years.

    it seems like the total difference in the DATE SETS can be had in DAYS very easily. But if you are then totaling DAYS and trying to report it in YEARs MONTHs DAYs ...... What is a year?

    1/1/2000 to 1/1/2001 is 366 days
    1/1/2001 to 1/1/2002 is 365 days

    total 731 days


    by the calendar it is two calendar years
    by days it is two years and a day

    Fact is they are both right!

    and is 6/1/2003 to 6/1/2004 a "year" ? Its actually 366 days. Where is the year? it is partly 2003 which is a 365 day year and partly 2004 which is a 366 day year. So what is it? Do you want to call that a year? a year and a day? Who's to say. One thing is for sure..... it's 366 days!

    Trying to use 365.25 assumes that you are spanning 4 years with a leap year in it. Span only three or span seven and it screws it up.

    Then you get into the month thing beyond that.

    Makes my brain hurt, Debs ! :(



    If you use:
    =INT(H8/365) & " YEARS " &INT(MOD(H8,365)/30.41667) & " MONTHS " & INT(MOD(MOD(H8,365),30.41667))& " DAYS " a more accurate factor included)

    I think you will get an accurate figure of DAYS every time. But I concede that because of a leap year thrown in somewhere, 731 days - which will be interpreted as 2 years and a day may, on the calendar, be just exactly two years.


    - Castleheart :)


    but it is STILL 731 days!
     
  15. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    My brain is a hurting too! Dates haunt me in Excel and and Access! They will be the breaking of me...bring the men in white coats!! :p

    Computers are supposed to be better than us yet I can work this out manually in no time, so why cant the computer do it for me??!! I suppose its cos I can't get it to understand what I want it to do. Let me clarify, for everyone:

    I need to enter the addresses a tenant has occupied along with the start and end dates each property was inhabited. There can be up to five properties, hence five sets of dates. I need the spreadsheet to find the differences between each set of dates (IE the time each property was occupied and then add these together and give an overall tenancy time for each tenant. Here is an example I was given from the user(obviously I have omitted the addresses):

    From To Address Years Months Days
    05/08/96 08/03/01 1 4 7 4
    31/07/95 04/08/96 2 1 0 5
    18/04/88 04/09/92 3 4 4 18
    23/06/86 17/04/88 4 1 9 26
    18/01/82 05/04/82 5 0 2 19

    Subtotal 10 22 72
    Total 12 0 10

    The problem I have with this is that the user uses a 31 day month to change the subtotal to a total! This in its self is not always gonna be accurate, is it!? Also, she counts the days inclusively (this could just be done by adding a day to the total days calculated). This is a really head messer upper!!!
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/172292

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice