Automatic rolling months for forecast sheet
 

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.

Automatic rolling months for forecast sheet
 

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.

--
Regards,
Tom Ogilvy


"Angus" wrote:

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.

Automatic rolling months for forecast sheet
 

Did you mean Date instead of DateSerial?
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Angus" wrote:

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.

Automatic rolling months for forecast sheet
 

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.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote:

Did you mean Date instead of DateSerial?
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Angus" wrote:

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.

Automatic rolling months for forecast sheet
 

Thanks both of you experts, it works now

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote:

Did you mean Date instead of DateSerial?
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Angus" wrote:

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.

Automatic rolling months for forecast sheet
 

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?

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote:

Did you mean Date instead of DateSerial?
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Angus" wrote:

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.

Automatic rolling months for forecast sheet
 

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.

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote:

Did you mean Date instead of DateSerial?
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Angus" wrote:

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.

Automatic rolling months for forecast sheet
 

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

"Angus" wrote:

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.

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote:

Did you mean Date instead of DateSerial?
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Angus" wrote:

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.