Excel 2000

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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?
 
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
 

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?
 

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?!
 

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?
 

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...
 
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
 
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
 
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
 
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 ;)
 

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!
 

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?
 
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!
 

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!!!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Members online

Top