4 Datedif Questions

[Mood]

I'm having a problem trying to keep track of a seniority list in XL2K. It calculates correctly, but doesn't display the way I would like it too. I have tried all possible combinations of formatting, with no success. The problems are in columns H and I.

The formula in Cell H4 is as follows:

=DATEDIF(J4,$A$3,"Y")&"." &DATEDIF(J4,$A$3,"YM")

A3 H4 I4 J4
October-01 17.1 17.01 Sep-84

A3 is the date for the calculations formatted as MMM-YY

H4 is the formula and shows a value of 17 Years 1 month, formatted as a number (2 decimal places)

I4 shows the fixed date, which I typed in by hand, and formatted as a number (2 decimal places). Would like to copy and paste from H4.

J4 shows the official start date formatted as MM-YY

Questions:

1) Can H4 show 2 decimal places e.g. xx.01 for one month, xx.02 for two months, xx.10 for ten months?

2) Why does I4 display differently when copied from H4, and pasted special, as values into I4? The difference shows up in the conditional formatting. I have it formatted as green, if it's over 30 years. If I type the value (17.1) in the cell, it stays normal, yet when pasted special, as a value, it show up green. I have tried all possible combinations of formatting, with no success.

3) Is it possible to show the copied value in H4 (17.1) as 17.01 in I4. Again, I have tried all possible combinations of formatting, with no success.

4) If all else fails, would a macro be able to retype the numbers in I4 so they would look right. There are about 300 to 400 rows to be changed. e.g. Changing 17.1 to 17.01 etc...

Thanks for looking into it, and helping...

Sorry for all the questions, but, I have wracked my brain into complete confusion…
Peter

[Yogi Anand]

On 2002-04-23 21:28, Mood wrote:
I'm having a problem trying to keep track of a seniority list in XL2K. It calculates correctly, but doesn't display the way I would like it too. I have tried all possible combinations of formatting, with no success. The problems are in columns H and I.

The formula in Cell H4 is as follows:

=DATEDIF(J4,$A$3,"Y")&"." &DATEDIF(J4,$A$3,"YM")

A3 H4 I4 J4
October-01 17.1 17.01 Sep-84

A3 is the date for the calculations formatted as MMM-YY

H4 is the formula and shows a value of 17 Years 1 month, formatted as a number (2 decimal places)

I4 shows the fixed date, which I typed in by hand, and formatted as a number (2 decimal places). Would like to copy and paste from H4.

J4 shows the official start date formatted as MM-YY

Questions:

1) Can H4 show 2 decimal places e.g. xx.01 for one month, xx.02 for two months, xx.10 for ten months?

2) Why does I4 display differently when copied from H4, and pasted special, as values into I4? The difference shows up in the conditional formatting. I have it formatted as green, if it's over 30 years. If I type the value (17.1) in the cell, it stays normal, yet when pasted special, as a value, it show up green. I have tried all possible combinations of formatting, with no success.

3) Is it possible to show the copied value in H4 (17.1) as 17.01 in I4. Again, I have tried all possible combinations of formatting, with no success.

4) If all else fails, would a macro be able to retype the numbers in I4 so they would look right. There are about 300 to 400 rows to be changed. e.g. Changing 17.1 to 17.01 etc...

Thanks for looking into it, and helping...

Sorry for all the questions, but, I have wracked my brain into complete confusion…
Peter

Hi Peter:
Let us first get to your first question first. Yes! change the formula in H4 to:

=DATEDIF(J4,$A$3,"Y")&IF(DATEDIF(J4,$A$3,"YM")>9,"-","-0")&DATEDIF(J4,$A$3,"YM")

Please note, I recommend changing "." to "-" or some other character of your choice to avoid confusion with a true decimal number --but since I don't know the context in which you are doing this, I will leave it upto you.

It is late and I am going to call it a night now, so your other questions will have to wait. Good Night!

[Qroozn]

2) make sure that when the values are entered they are both entered as text, or number. and reenter the value into the cells.

3)try the following formula which will convert a number to months so 12months =.12, 3 months = .03. I had trouble on 10months. hope it helps

=IF(A1-ROUND(A1,0)=0.109999999999999,0.11,IF(A1-ROUND(A1,0)=0.119999999999999,0.12,IF(A1-ROUND(A1,0)>0,(A1-ROUND(A1,0))*0.1,IF(A1-ROUND(A1,0)<0,(A1-ROUND(A1,0)+1)*0.1))))

[Aladin Akyurek]

Peter,

Try:

=DATEDIF(J4,$A$3,"Y")&"." &RIGHT(0&DATEDIF(J4,$A$3,"YM"),2)

Aladin

[Mood]

[/quote]

Please note, I recommend changing "." to "-" or some other character of your choice to avoid confusion with a true decimal number --

[/quote]

Thanks Aladin and Yogi, both ideas worked for the first question.

Yogi's idea of changing the decimal to something else was the thing for all my frustration and the root of my problem.

Any ideas as to the other questions?
Much thanks, Peter

[Aladin Akyurek]

On 2002-04-24 06:40, Mood wrote:

Please note, I recommend changing "." to "-" or some other character of your choice to avoid confusion with a true decimal number --

[/quote]

Thanks Aladin and Yogi, both ideas worked for the first question.

Yogi's idea of changing the decimal to something else was the thing for all my frustration and the root of my problem.

Any ideas as to the other questions?
Much thanks, Peter
[/quote]

Questions 1 & 3 are the same, no?

The 3rd question is a bit unclear to me. Are you trying to apply cond format to the cells housing the formula results?

Aladin

[Mood]

On 2002-04-24 07:31, Aladin Akyurek wrote:

Please note, I recommend changing "." to "-" or some other character of your choice to avoid confusion with a true decimal number --

Thanks Aladin and Yogi, both ideas worked for the first question.

Yogi's idea of changing the decimal to something else was the thing for all my frustration and the root of my problem.

Any ideas as to the other questions?
Much thanks, Peter
[/quote]

Questions 1 & 3 are the same, no?

The 3rd question is a bit unclear to me. Are you trying to apply cond format to the cells housing the formula results?

Aladin
[/quote]

Actually, the solving of the first question , also solved the 3rd and 4th question, sorry for not been clearer.

The only remaining question (#2) is why the conditional formating in both H & I columns won't work. I think it might have to do with the values in H are not numbers. Could be wrong...
Peter