| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| template of a rolling forecast for production? | Excel Worksheet Functions | |||
| ROLLING 12 MONTHS PLEASE HELP ME!!!!! | Excel Discussion (Misc queries) | |||
| Rolling Forecast | Excel Worksheet Functions | |||
| I look for templates to plan rolling forecast allocation | Excel Discussion (Misc queries) | |||
| how do I forecast a 15% increase over 7 months? | Excel Discussion (Misc queries) | |||
Hybrid Mode
