1. Hi Guest: Welcome to TRIBE, Toronto's largest and longest running online community. If you'd like to post here, or reply to existing posts on TRIBE, you first have to register on the forum. You can register with your facebook ID or with an email address. Join us!

Excel nerd assistance required.

Discussion in 'Technology' started by daddyiwantchocolate, Oct 13, 2005.

  1. daddyiwantchocolate

    daddyiwantchocolate TRIBE Member

    Question:

    Cell A1 = date (in random date format). Let's say this says January 1, 2005.

    Cell A2 = I need to count 37 weeks AFTER January 1, 2005.

    How in the shit would I do this?

    I've found formulae for counting days BEFORE a date, but none calculating time going forward.

    Frankly, my brain cannot wrap itself around this one right now, and your help is really appreciated!!!!!
     
  2. Liquidity

    Liquidity TRIBE Member

    =DATE(YEAR(A1), MONTH(A1), DAY(A1)+(37)*7)
     
  3. daddyiwantchocolate

    daddyiwantchocolate TRIBE Member

    <3

    Thanks sooooo much!!!! : )
     
  4. daddyiwantchocolate

    daddyiwantchocolate TRIBE Member

    Alright, one last question for a while:

    A1/2/3 = earlier date , B1/2/3 = later date,
    C1/D1 (read:combination of a couple of columns) = length of time between now and another date, expressed in years and months..

    So if A1 = January 1, 1966,
    And B1 = October 15, 2005
    I'd need C1/D1 to be 39 years/9 months

    This one has me (again) stumped, because nothing I've tried is working and I can't figure out what the fuck I'm missing.
     
  5. daddyiwantchocolate

    daddyiwantchocolate TRIBE Member

    Waitaminute, I think I may have cracked it.
    EDIT: nope.

    entering rage phase.
     
  6. Liquidity

    Liquidity TRIBE Member

    cell c1 is to get the years:

    =(YEAR(B1)-YEAR(A1)) [formatted numbers]

    cell d1 is to add the above years to the first date:

    =DATE(YEAR(A1) + C1, MONTH(A1), DAY(A1)) [formatted date]

    cell e1 is to subtract the months:

    =MONTH(B1) - MONTH(D1)


    NOTE: i didn't add in any IF statements in case A1's month is higher than B1's. I think you can figure it out easily enough..
     
  7. Liquidity

    Liquidity TRIBE Member

    additionally:

    you could just put this in D1:

    =MONTH(B1) - MONTH(DATE(YEAR(A1) + C1, MONTH(A1), DAY(A1))) [formatted number]

    looks messy tho
     

Share This Page