Solved: excel dates

Not open for further replies.

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

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.

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?

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.

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

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.

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

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

As Seen On