Automatic rolling months for forecast sheet

[Guest]

I have to make a sales forecast sheet with rolling months, so that when sales
type "Aug-07" at A1, A2 becomes "Sep-07", and A3 becomes "Oct-07" and so on...

However, i am not sure will the salesman type "Aug-07" or "August-07" or
08012007" at A1...

Besides, i want the month (and the rolling months) a REAL DATE format
because i will copy it to Access database for statistic purpose.

How to make this happen, please help.

 

[Guest]

If it is to be a real date, then it must be a specific day in the month. If
the user enters just the month and year, then it will default to the 1st day
of the month.

in A2 put =if(A1<>"",DateSerial(year(a1),Month(A1)+1,1),"")

and format the cell in the date format you want to see. then drag fill down
the column.

 

[Guest]

Did you mean Date instead of DateSerial?

 

[Guest]

Why yes I did. DateSerial would be the VBA equivalent. So to the OP,
replace DateSerial with Date in the formula.

Thanks for the catch.

 

[Guest]

Thanks both of you experts, it works now

Why yes I did. DateSerial would be the VBA equivalent. So to the OP,
replace DateSerial with Date in the formula.

Thanks for the catch.

 

[Guest]

Thanks for your reply.

But as we know that in excel Sept 1, 2007 means 39326, it is not a real date
format. I need to copy that to access database and hopefully in a real date
format (and need to retrieve data from database to excel later for
reporting). How to make it?

 

[Guest]

Both experts,

The rolling months work, but when I update to Access database, the value is
empty. Following is my code:

.AddNew
.Fields("Order_month") = Trim(Range("J3").Value)
.Update

I try to replace with .Fields("Order_month") = Trim(Range("J3").Text) but
doesn't work. Other cells update properly to database. Thanks to help.

 

[Guest]

Sorry, my mistake. The code is addressing to the wrong cell.