# YEARFRAC question

With this formula...is it possible to show when a date has been moved either forward or backwards with some method? Color-coding??
=YEARFRAC(I683,K683,1)*12
###### Who is Participating?

Commented:
Yes just reverse the cells in the SIGN function like this

=YEARFRAC(I683,K683,1)*12*SIGN(I683-K683)

regards, barry
0

Commented:
Not sure what you mean by "moved forward or back" - how does that manifest itself in the formula result?

Barry
0

Author Commented:
To determine whether the date being compared is ahead or behind the original date...it does calc correcly, but I can't tell at glance if the compared date is ahead or behind the orginal date.

A                               B                      C                         D                 E
12/01/2011              08/01/2011        03/01/2011          8                 5(this is before col B by 5 months)
0

ConsultantCommented:
You can do a simple comparison of the two dates:

=IF(C1<B1,"Before","After")

Kevin
0

ConsultantCommented:
You can use the above formula in a conditional formatting rule to set the color of the cell.

Kevin
0

ConsultantCommented:
See attached example.
Q-26891394.xlsx
0

Commented:
If you want you could make the results positive or negative, e.g. by using

=YEARFRAC(I683,K683,1)*12*SIGN(K683-I683)

regards, barry
0

Author Commented:
Barry, works great...except the negative and positive are reversed. No big deal, but is the fix easy?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.