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.

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.

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.

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.

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.

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.

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.

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.