Date formula that does not go into the other month

[Fin Fang Foom]

Hi everyone,

Does anyone has an idea how to do this ?

I input this date in cell C2 10-01-2005 and in D2 I put this formula in =TEXT(C2+1,"mm-dd-yyy") then I copy the formula across the columns to AG. Now If I do this for the month of November it will only have 30 days in that month. So in cell C2 If I change it to 11-01-2005 in cell AG2 it will show 12-01-2005 I dont want to show next month is there a way to have it blank ? So I if input in cell C2 02-01-2005 then the formula calculates accross the columns and stops at 02-29-2005.

Does somebody have a formula for that ?

 

[Von Pookie]

Try:

=IF(C2="","",IF(MONTH(C2+1)<>MONTH(C2),"",TEXT(C2+1,"mm-dd-yyy")))

 

[nbrcrunch]

You could use an IF() statement that checks the value of the month. If it is different than your first cell in the series, then leave the cell blank.


May I ask why you are using a date and then converting it to text?

 

[Fin Fang Foom]

Thanks Von Pookie it works perfect.

nbrcrunch,

The reason I'm converting Date to a text format is I'm doing a VLOOKUP formula. The files are saved as a Date text format. The reason the users save it that way because its easier to look for on the network.