# Trying to pick the cell with 2013 date from two cells then applying yearfrac calculation

1. ## Trying to pick the cell with 2013 date from two cells then applying yearfrac calculation

This is what I have for data

A B C
02/02/2007 10/09/2013
03/02/2010 21/12/2015
04/05/2013

I want cell C to look at A and B dates in the same row and pick the one that has a year of 2013. Then take that cell and calulate the (yearfrac(start_date),end_date,basis) function using start_date = cell with date of 2013 and end date = Dec 31, 2013

Can anyone help?  Register To Reply

2. ## Re: Trying to pick the cell with 2013 date from two cells then applying yearfrac calculati

Something like this...

=IF(OR(YEAR(A2)=2013,YEAR(B2)=2013),YEARFRAC(IF(YEAR(A2)=2013,A2,B2),DATE(2013,12,31)),"")  Register To Reply

3. ## Re: Trying to pick the cell with 2013 date from two cells then applying yearfrac calculati

Hello Tony this works but I was wondering if it is possible to add one more condition to the calculation step.

If date in A2 is 2013 yearfrac from date to dec 31 2013
If date in B2 is 2013 yearfrac jan 1 2013 to date

Is this possible?  Register To Reply

4. ## Re: Trying to pick the cell with 2013 date from two cells then applying yearfrac calculati

Maybe this...

=IF(OR(YEAR(A2)=2013,YEAR(B2)=2013),YEARFRAC(IF(YEAR(A2)=2013,A2,DATE(2013,1,1)),IF(YEAR(A2)=2013,DATE(2013,12,31),B2)),"")  Register To Reply

5. ## Re: Trying to pick the cell with 2013 date from two cells then applying yearfrac calculati

This is great!! One last thing, if neither applies can I populate the cell with number 1?  Register To Reply

6. ## Re: Trying to pick the cell with 2013 date from two cells then applying yearfrac calculati

At the very end of the formula replace "" with 1.  Register To Reply

7. ## Re: Trying to pick the cell with 2013 date from two cells then applying yearfrac calculati

Perfect!!! Thanks a million   Register To Reply

8. ## Re: Trying to pick the cell with 2013 date from two cells then applying yearfrac calculati

You're welcome. Thanks for the feedback!   Register To Reply