+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    09-11-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    30

    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. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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)),"")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    09-11-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    30

    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. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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. #5
    Registered User
    Join Date
    09-11-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    30

    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. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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. #7
    Registered User
    Join Date
    09-11-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    30

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

    Perfect!!! Thanks a million

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 01-25-2013, 04:59 PM
  2. How to: Click on a cell, calendar pops up, pick a date, date appears in cell.
    By jonvanwyk in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-08-2011, 01:56 PM
  3. Replies: 5
    Last Post: 04-01-2011, 07:17 AM
  4. Finding the first non-blank cell in a row and then applying a calculation
    By michaelrepper@hotmail.com in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-24-2006, 11:15 AM
  5. Applying same calculation on various cells
    By AndyOne in forum Excel General
    Replies: 2
    Last Post: 03-09-2005, 09:56 AM

Bookmarks

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