1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: excel dates

Discussion in 'Business Applications' started by nomad, Sep 17, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. nomad

    nomad Thread Starter

    Joined:
    Sep 30, 1999
    Messages:
    386
    I would like to build a spread sheet so when I enter a date the adjacent cell will have a date according to some rules. For example

    Dates entered between Jan-01-2013 and Mar-31-2013 the adjacent cell will have a date of July-01-xxxx (where xxxx is the same year as the entered date. Can someone help me out with this.

    thanx
     
  2. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    61,524
    First Name:
    Chuck
    Assuming you will be entering dates in column A, starting at row 1, and want the results in column B, format the cells in both columns to match your desired date format.

    Then try the following formula in cell B1:
    Code:
    =IF(AND(MONTH(A1)>0,MONTH(A1)<4),(DATE(YEAR(A1), 7,1)),"")
    Then drag copy the formula down column B as far as needed.

    The formula checks to see if the given month is Jan, Feb, or Mar, and does not check for the day of the month. Based on your description of your requirements, I assumed you were interested in whole months only. Any month out of that range returns a blank cell in column B. You could use stacked IF( functions to have additional results in column B for different groups of months.
     
  3. nomad

    nomad Thread Starter

    Joined:
    Sep 30, 1999
    Messages:
    386
    I tried to lump these together because I actually have to do four sets, 1 for each quarter but it didnt work for me. The dates are as follows:

    Between Jan-01-2013 and Mar-31-2013 the adjacent cell will have a date of July-01-xxxx
    Between Apr-01-2013 and Jun-31-2013 and the adjacent cell will have a date of October-01-xxxx
    Between Jul-01-2013 and Sep-30-2013 the adjacent cell will have a date of January-01-xxxx
    Between Oct-01-2013 and Dec-31-2013 the adjacent cell will have a date of April-01-xxxx

    Is this possible to do in one formula or would I need a lookup table or use VBA?

    thanks for your help
     
  4. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    61,524
    First Name:
    Chuck
    In those two cases, does xxxx need to be the year following the given year (i.e. Jul-01-2013 and Sep-30-2013 the adjacent cell will have a date of January-01-2014)?

    This could be done without any VBA and might be simpler using a VLOOKUP( table function within a formula) rather than stacked IF( functions.
     
  5. nomad

    nomad Thread Starter

    Joined:
    Sep 30, 1999
    Messages:
    386
    yes... good catch... thx
     
  6. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    61,524
    First Name:
    Chuck
    That makes it easier. Try this formula in cell B1 and then drag copy it down the column as far as needed:
    Code:
    =DATE(YEAR(A1),INT((MONTH(A1)-1)/3)*3+7,1)
    I found a way to calculate the desired month without any If( or VLOOKUP( functions and used a bit of modular math instead. Excel automatically increments the year in the DATE( function if the month number is higher than 12.
     
  7. nomad

    nomad Thread Starter

    Joined:
    Sep 30, 1999
    Messages:
    386
    worked like a charm... thank you so much
     
  8. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    61,524
    First Name:
    Chuck
    You're welcome.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1108684

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice