# 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?

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)),"")

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?

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)),"")

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?

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.

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

Perfect!!! Thanks a million

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

You're welcome. Thanks for the feedback!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1