# 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.

#### CastleHeart

DOCTOR.....my BRAIN HURTS !

Again!!!

Hey do Debs!

31 days won't be accurate.
30 days won't be accurate.
A month is a month on the calendar.

The two formulas will differ only slightly over a given time span.
The formula of:
=(DATEDIF(A2,B2,"Y")&" years "&(DATEDIF(A2,B2,"YM")&" months "&((DATEDIF(A2,B2,"MD")+1)&" days")))
gave the same results as whatever you are using for her. That is accurate as to D/M/Y and takes the calendar into consideration.

The "DAYS" component is never more than 30 per time period.

So the inaccuracy can't be but so much. Actually probably never more than a day per time period - so in your example - in the most extreme case, assuming EVERY time period of the 5 happened to end up with 30 days!) she would be off by 5 days. But that is on the outside and usually it will be very close.

When you leave the calendar behind and start totaling DAYS that are now "Calendarless" - you'll just have to pick a factor and go with it.

Tell her to live with it!

- Castleheart

#### Erm

Thanks for all your help CastleHeart! She is gonna have to live with a little imperfection! What she had wasn't perfect anyhoo! At least this works out the imperfect answer for her! What more do these darn users expect when they work with dates!!!!!

Thanks for all the help, hope ur about when I get my next Excel dilemma!!!!!

#### Erm

That well known law applies! The second I had given up I found the best possible solution (its not far from perfect). I now have a spreadsheet that calculates the figures below, except for the overall total,(but she can calculate this in seconds):

From - To
05/08/96 - 08/03/01
31/07/95 - 04/08/96
18/04/88 - 04/09/92
23/06/86 - 17/04/88
18/01/82 - 05/04/82

Dif Between Dates Above
4yrs 7mths 4days
1yr 0mths 5days
4yrs 4mths 18days
1yr 9mths 26days
0yrs 2mths 19days

Subtotal(Dif) = 10yrs 22mths 72days
Total = 12yrs 0mths 10days

So, she can just calculate the Total mentally for each tenant!!

The formula is:
=((DATEDIF(D4,E4,"Y"))+(DATEDIF(H4,I4,"Y"))+(DATEDIF(L4,M4,"Y"))+(DATEDIF(P4,Q4,"Y"))+(DATEDIF(T4,U4,"Y")))&" years "&((DATEDIF(D4,E4,"YM"))+(DATEDIF(H4,I4,"YM"))+(DATEDIF(L4,M4,"YM"))+(DATEDIF(P4,Q4,"YM"))+(DATEDIF(T4,U4,"YM")))&" months "&((DATEDIF(D4,E4,"MD"))+1)+((DATEDIF(H4,I4,"MD"))+1)+((DATEDIF(L4,M4,"MD"))+1)+((DATEDIF(P4,Q4,"MD"))+1)+((DATEDIF(T4,U4,"MD"))+1)&" days"

Which I think I had before but never realised it gave me the subtotal that she gets as I hadn't tried it with the example dates!!! Doh!

There is now only one problem, as she calculates the dif inclusively, I have the formula adding 1 day to the days bit of each difference. So...if the tenant has been in only 1 property the other four start dates and end dates are empty so give a value of 1. Then making the subtotal out by 4 days...is there a way I can get the formula to ignore the null fields? If I have explained this as badly as I think I have, please ask for clarification!

Thanks sooooo much to everyone! You are all great!

#### CastleHeart

Debs:

Two steps:

Calculate total days corrected for (+1) and (No Occupancy):

=IF(B2-A2>0,((DATEDIF(A2,B2,"D"))+1),0)+IF(B3-A3>0,((DATEDIF(A3,B3,"D"))+1),0)+IF(B4-A4>0,((DATEDIF(A4,B4,"D"))+1),0)+IF(B5-A5>0,((DATEDIF(A5,B5,"D"))+1),0)+IF(B6-A6>0,((DATEDIF(A6,B6,"D"))+1),0)

Then convert them:

=INT(C7/365) & " YEARS " &INT(MOD(C7,365)/30.41667) & " MONTHS " & INT(MOD(MOD(C7,365),30.41667))& " DAYS "

EDIT: The C7 referred to above is the cell that contains the first formula (and therefore it's result)

I have an .XLS that might be of help if you want a PM or send me an address. Can't post .XLS's. If not that's cool 2.

- Castleheart

#### Erm

You are a god CastleHeart! That worked perfectly, Thanks again!

#### CastleHeart

Wow!

I got a promotion!

Hope it comes with a pay raise!

- C

#### Erm

I'm afraid its purely voluntary...but think of the angels!!!

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.

As Seen On