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
singleton2787Asked:
Who is Participating?
 
barry houdiniCommented:
Yes just reverse the cells in the SIGN function like this

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

regards, barry
0
 
barry houdiniCommented:
Not sure what you mean by "moved forward or back" - how does that manifest itself in the formula result?

Barry
0
 
singleton2787Author 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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
zorvek (Kevin Jones)ConsultantCommented:
You can do a simple comparison of the two dates:

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

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
You can use the above formula in a conditional formatting rule to set the color of the cell.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
See attached example.
Q-26891394.xlsx
0
 
barry houdiniCommented:
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
 
singleton2787Author 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.

All Courses

From novice to tech pro — start learning today.