Configuring dates

[jesscar_me]

Hi there,

I'm just wondering if anyone can give me a hand figuring out how to make a certain type of date automate. It's kind of hard to explain, but basically, I'd like to make it so that when I enter a date in one column, another column will automatically populate with the 1st of the next month. For example:

If I enter 4/26/2009 in the 1st column, column 2 will read: 5/1/2009
If I enter 1/19/2008 .................................................. 2/1/2008

Does that make sense?

Also, it's very important that if the FIRST date is already the first of the month, then the second column will read the same. For instance:

If I enter 3/1/2009 in the first colum, the second column will ALSO read 3/1/2009.

Please let me know if you can give me a hand or any advice on where I can find the information on how to do this. THANK YOU!!!!

[shg]

Welcome to the forum.

=if(a1="", "", a1 - day(a1) + 1)

[jesscar_me]

Hey, thanks so much!! Actually, there was a bit of a glitch with that, in that the formula gave me the 1st of the same month as the original date, but what it did do was point me in the right direction to the formula I ended up using:

=IF(DAY(I4)=1,DATE(YEAR(I4),MONTH(I4),DAY(I4)),DATE(YEAR(I4),MONTH(I4)+1,1)

I honestly had no idea how the dates worked before this, so thanks again!

[shg]

the formula gave me the 1st of the same month as the original date

Sorry, I misread your question.

... I ended up using:

=IF(DAY(I4)=1,DATE(YEAR(I4),MONTH(I4),DAY(I4)),DATE(YEAR(I4),MONTH(I4)+1,1)

Good job. You can simplify that to:

=IF(DAY(I4)=1, I4, DATE(YEAR(I4), MONTH(I4)+1, 1)

If you install the Analysis ToolkPak add-in (via Tools > AddIns) you can use

=IF(DAY(I4)=1, I4, EOMONTH(I4, 0) + 1)