I am using Concatenate Date Value In One Cell

[moongirle2o12]

Hello friendes

I am facing a problem on Excel function I am using Concatenate Date Value In One Cell to increse the year of C2 cell if the month in B2 cell is equal to 12 and if the month of B2 cell is not equal to 12 it should increase the month only if C2 cell


i have pased the function that I am using please try to correct it


C2 =IF(MONTH(B2)=12,DATEVALUE(CONCATENATE("1/1/",YEAR(B2)+1)),DATEVALUE(CONCATENATE(MONTH(B2)+1,"/1/",YEAR(B2))))



I want to increase the year in C2 if the month in B2 is 12 and increase the month



IF the month in B2=12, true increase the year only in C2, false increase the month only in C2



The above function work fine and increase the year if the month in B2=12. but it is not increasing the month only if the month in B2 is (1,2,3,….,11) it is fix as Jan



However I tried to modify the function to increase the month only by applying the below modification but it give error



C2 =IF(MONTH(B2)=12,DATEVALUE(CONCATENATE("1/1/",YEAR(B2)+1)),DATEVALUE(CONCATENATE(“1/", MONTH(B2)+1,YEAR(B2))))





Can you help me Urgently in this issue

[Sisyphus]

Hi,
I'll look at your precise request what I am at a PC a little later, but offhand it looks to me like you might get the better solution if you would post the problem. Why do you want to concatenate a Datevalue while all you have to do is add one month to any December date to get a date in January with the year changed, or add one year to that same date to change the year?
A date is stored as a number, say 35320, which represents the 35320th day since Jan 1, 1901. If you add 30 to that number the date you get is 30 days Dow the time channel, and if you write it formatted it will have the month and year of that day, counting from the same base last century. With your concatenate function you extract the year of a date number you have and introduce it to a new number you create from a text string. It should work, and I'll get back to you on that. But it sounds awfully complicated.

[Sisyphus]

Hi,
The principal error in your formula is in the second CONCATENATE function. You forgot to insert the slash beteen month and year. In addition, the posted formula has a pair of inverted commas instead of quotation marks to open the string "1/". Here is the formula that works the way you want:

Code: Select all

=IF(MONTH(B2)=12,DATEVALUE(CONCATENATE("1/1/",YEAR(B2)+1)),DATEVALUE(CONCATENATE("1/", MONTH(B2+1),"/",YEAR(B2))))


As I said earlier, I wouldn't have used the CONCATENATE function at all in that instance. How about this one:

Code: Select all

=IF(MONTH(B2) = 12, DATE(YEAR(B2)+1, 1, 1), DATE(YEAR(B2), MONTH(B2) + 1, 1))


or this one:

Code: Select all

=EOMONTH(B2,0) + 1


I feel that this last formula probably does what you want. It writes the date of the first day of the next month which, in this case, is calculated by finding the end of the current month and adding one day to it. You don't have to worry about the year. Day No. 40907 was the 31st day of December 2011. Add 1 day and you ar on the 1st day of January 2012. Excel knows that.

BTW, you need to load the Analysis Toolpack in order to be able to use the EOMONTH function. Checkmark the Toolpack in your Tools/Add-ins list.

The other function to keep an eye on while you are juggling dates is the EDATE function.

Code: Select all

=EDATE(B2,1)


will add one month to the date in B2. Again, you don't need to worry about years, but the result will be the same day of each month as is found in B2.
I hope this will enable you to face 2012 with confidence!
Have a great year.

[moongirle2o12]

Hi dear Sisyphus

Thank you for your great help i have used the second code and it works as i need

Code: Select all
=IF(MONTH(B2) = 12, DATE(YEAR(YEAR(B2)+1), 1, 1), DATE(YEAR(B2), MONTH(B2) + 1, 1))

but I had to remove the red Year from the code"Year" and it works fine

Thanks a lot

[Sisyphus]

Hi,
Thanks for pointing out the duplication.
I have amended the formula in my original post for the benefit of others who may want to use it.
Have a great day!