Date functions using if

[ibesmond]

I have a couple of questions.

I'm trying to create a formula so that I can use a reference cell to adjust another cell.

for instance. Say I have three dates 5/10/03, 4/28/05 and 9/8/08. I want other cell to return the dates 5/10/10, 4/28/10 and 9/8/10 where the year is constantly 2010. is that possible?

One more question. Im trying to create an if function where if the first date is between any month and the 1-15th day, any year, to return that month, 15th, and that year. for example I have three dates, 10/4/10, 4/14/06, 9/3/09, I want the formula to return 10/15/10, 4/15/06 and 9/15/09. Now if the date is between the 16th and the last day of the month, I want the formula to return the same month, last day of the month, and same year. For example I have three months, 6/20/10, 7/18/08, 2/25/09. I want the formula to return 6/30/10, 7/31/08 and 2/28/09. Is that possible?

Thank you for any responses.

 

[xenou]

In the first case,

=Date(2010,Month(A1),Day(A1))

In the second case:

=If(Day(A1)<=15,Date(Year(A1),Month(A1),15),Date(Year(A1),Month(A1)+1,0))

The last formula contains a very useful trick. This formula essential tells Excel to add one to the month, and take the day before (hence, the last day of the month before it):
=Date(Year(A1),Month(A1)+1,0)
If the zero is hard to understand at first, its basically the same as this one, which explicitly gives the first of the month in the date formula, and subtracts one from it to get to the previous day:
=Date(Year(A1),Month(A1)+1,1) - 1

Edit:
Note that while we're on the subject, I'd like to post a formula for getting the last day of the next month - which is great for filling in formulas when you need to get a series of month end dates (and filling the series isn't possible):
=Date(Year(A1),Month(A1)+2,0)