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?

Improve company productivity with a Business Account.Sign Up

x
 
barry houdiniConnect With a Mentor Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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.