Convert Text Dates To DateSerial And Sort

[aideycorc]

I need help with some formatting of data. This is going to be quite difficult to explain but I shall give enough underlying information to explain what I need to happen in full.

I have a database which produces a sheet of raw data of the invoiced amounts from different areas each month. The person who will be using the spreadsheet is not excel trained to much knowledge and only knows basic skills, i.e copy & paste.

The breakdown of the spreadsheet is;

Workbook 1: Raw data that will be processed every month.

Workbook 2: A huge table with many clients that remains static other than the import of sheet 1’s information each month.

1. What I need to happen is for Workbook 2 to become practical and basically ‘automatic’ in picking up the data from Workbook 1 when it is generated.

I was thinking this could be done by possibly creating some sort of Match or V/Hlookup, or even a macro, on a new sheet in Workbook 2 where the person using the Spreadsheet will just be able to copy and paste into Workbook 2 after it has been generated each month. With these figures then being picked up automatically, I can imagine this getting very complex. The problems may start because the invoiced amounts each month are different clients and some clients may appear missing as this would mean they haven’t been invoiced during that month, as you can see in my sample SS.

I have attached a workbook to this post with Workbook 1 represented as Sheet 1 and the same with Workbook 2 and Sheet 2.

2. Once the above has been done, I then have a table at the beginning of Workbook 2 which needs to pick up the commencement date of the client and their invoiced amounts. This needs to be done on a 5 year turnover basis and needs to pick up the information that is being imported (somehow) each month.

Could you please tell me if this is possible (which is probably is, just to a certain degree of complexity) and have a go at it so I can mirror what has been done?

I shall colour coordinate things on my sample spreadsheet, this can be changed when you are having a go  ..

Thanks, much appreciated.

10889

[SamT]

Confusing: You mention "Areas" and "Clients" but it sounds like "Areas" is just another word for "Clients." Please clarify what you meant with these two words.

If I may rephrase your problem: You have an autogenerated workbook with data from a database and you need to import that data into a 5year report workbook.

It is easy to import the data into the exact cell it needs to be in when the 5 year workbook is opened the first time after the autogenerated workbook is created. It can also be accomplished with the user clicking a button on the 5 year worksheet.

This mainly requires that the autogenerated book always be put in the same folder and that its nameing comvention and pattern be known.

It is also possible to bypass the autogenerated workbook and import the data directly from the database.

The main problem with this scheme is the mechanics of the 5 year turnover event(s.)

Unfortunately, my "office" computer is down, so I can't offer any concrete help. Others will chime in, however, and we will get you on your way.

[aideycorc]

Confusing: You mention "Areas" and "Clients" but it sounds like "Areas" is just another word for "Clients." Please clarify what you meant with these two words.

If I may rephrase your problem: You have an autogenerated waorkbook with data from a database and you need to import that data into a 5year report workbook.

It is easy to import the data into the exact cell it needs to be in when the 5 year workbook is opened the first time after the autogenerated workbook is created. It can also be accomplished with the user clicking a button on the 5 year worksheet.

This mainly requires that the autogenerated book always be put in the same folder and that its nameing comvention and pattern be known.

It is also possible to bypass the autogenerated workbook and import the data directly from the database.

The main problem with this scheme is the mechanics of the 5 year turnover event(s.)

Unfortunately, my "office" computer is down, so I can't offer any concrete help. Others will chime in, however, and we will get you on your way.

Correct: When I referred to 'areas' this was another term for the client yes. Thanks for noticing my wording error.

Yes, except the five year period will not be a fixed period i.e calendar year. This will vary depending on the date the client is first enrolled. (The start date on workbook 2 in the 'A' column).

I understand your different ways. What would be less problematic? Having the information put into a new worksheet in the workbook of the main table? Or saving all generated reports into the same folder and it being picked up from there?

Thank you for your input

[SamT]

I don't understand what you mean by "Turnover," so I don't know how to calculate it.

When you say
pick up the commencement date of the client and their invoiced amounts. This needs to be done on a 5 year turnover basis I think you mean that you want the annual "Turnover" for the previous 5 calendar years for each client, at least back to their commencement date.

IOW,


For a client that started 6 months ago, their will be no "Turnover" value.
For a client that started 2.999 years ago, there will be two years "Turnover" values,
And for that client that started 6 years ago, there will be five years "Turnover" values.


Personally, in the interests of consistency and completeness, I would add one more "Turnover" column, to wit; "Turnover To Date, This Period."

To reiterate, The "Turnover" periods for each client are based on that clients Commencement Date


The following is based on this statement

As I understand, the 60 Monthly columns are only used to provide the data use to compute the values for the 5 "Turnover" columns. That they are not used for any other purpose.

Report Table Columns


Commencement Date, especially Month and Day
Client Name
Blank
TurnOver 1
TurnOver 2
TurnOver 3
TurnOver 4
TurnOver 5
TurnOver To Date, This Period


As a new project using SQL directly from the Database, you would first code to retrieve the previous 5+ years of data. If you stop coding at this point, you merely have to run the code once a month to completely rebuild the table. You can also run the code intermittently to bring it up to date on any given day. This would remove almost all User activity from the workbook. The user would merely click a button or run the macro from the menu. It could otherwise be run on Workbook Open as mentioned above.

If rebuilding the report took too long, the code's parameters could be changed to only retrieving the current periods data. But more code would have to be added to maintain the "turnover" columns.

If you merely want to automate the current project, I would move the monthly data columns to a separate sheet in the interest of readability of the report. This sheet would be automatically updated from the monthly autogenerated workbook. The code could only be run once a month and must have checks built in to insure that it fails if this month's data has already been retrieved.

Data Table Columns


Client Name
Blank
60
59
58
...
1

The code would first delete the data cells, (Shift Left,) in column named 60. Then it would import the current month's data into column named 1. The code must verify that the imported data is in the proper client's Row.

The six "Turnover" formulas on the Report sheet might be

=SUM(DataSheet!$C2:$N2) = Year1
=SUM(DataSheet!$O2:$Z2) = Year2
=SUM(DataSheet!$AA2:$AL2) = Year3
=SUM(DataSheet!$AM2:$AX2) = Year4
=SUM(DataSheet!$AZ2:$BJ2) = Year5
=SUM(DataSheet!$BL2:$BV2) = Period To Date


Note that each formula is copied down to the Row of the bottom of the client list.
Note that each formula Sums 12 months' data.
Note that I may be misunderstanding how to calculate "Turnover."

It is up to you to decide which method you want to use.

I prefer the SQL Direct From Database method because the code is easier to maintain, being all in one block. That it needs no references to anything other than the database, that it can automatically update the Report when clients are added or changed.

I believe that the Report Sheet + DataSheet + autogenerated update sheet + Formulas is easier for a beginner to understand. Such understanding is required to maintain the system. However, this method is subject to "Feature Creep" which can necessitate a compete overhaul and/or replacement in the unseen future.

[aideycorc]

I don't understand what you mean by "Turnover," so I don't know how to calculate it.

When you say I think you mean that you want the annual "Turnover" for the previous 5 calendar years for each client, at least back to their commencement date.

IOW,


For a client that started 6 months ago, their will be no "Turnover" value.
For a client that started 2.999 years ago, there will be two years "Turnover" values,
And for that client that started 6 years ago, there will be five years "Turnover" values.


Personally, in the interests of consistency and completeness, I would add one more "Turnover" column, to wit; "Turnover To Date, This Period."

To reiterate, The "Turnover" periods for each client are based on that clients Commencement Date


The following is based on this statement


Report Table Columns


Commencement Date, especially Month and Day
Client Name
Blank
TurnOver 1
TurnOver 2
TurnOver 3
TurnOver 4
TurnOver 5
TurnOver To Date, This Period


As a new project using SQL directly from the Database, you would first code to retrieve the previous 5+ years of data. If you stop coding at this point, you merely have to run the code once a month to completely rebuild the table. You can also run the code intermittently to bring it up to date on any given day. This would remove almost all User activity from the workbook. The user would merely click a button or run the macro from the menu. It could otherwise be run on Workbook Open as mentioned above.

If rebuilding the report took too long, the code's parameters could be changed to only retrieving the current periods data. But more code would have to be added to maintain the "turnover" columns.

If you merely want to automate the current project, I would move the monthly data columns to a separate sheet in the interest of readability of the report. This sheet would be automatically updated from the monthly autogenerated workbook. The code could only be run once a month and must have checks built in to insure that it fails if this month's data has already been retrieved.

Data Table Columns


Client Name
Blank
60
59
58
...
1

The code would first delete the data cells, (Shift Left,) in column named 60. Then it would import the current month's data into column named 1. The code must verify that the imported data is in the proper client's Row.

The six "Turnover" formulas on the Report sheet might be

=SUM(DataSheet!$C2:$N2) = Year1
=SUM(DataSheet!$O2:$Z2) = Year2
=SUM(DataSheet!$AA2:$AL2) = Year3
=SUM(DataSheet!$AM2:$AX2) = Year4
=SUM(DataSheet!$AZ2:$BJ2) = Year5
=SUM(DataSheet!$BL2:$BV2) = Period To Date


Note that each formula is copied down to the Row of the bottom of the client list.
Note that each formula Sums 12 months' data.
Note that I may be misunderstanding how to calculate "Turnover."

It is up to you to decide which method you want to use.

I prefer the SQL Direct From Database method because the code is easier to maintain, being all in one block. That it needs no references to anything other than the database, that it can automatically update the Report when clients are added or changed.

I believe that the Report Sheet + DataSheet + autogenerated update sheet + Formulas is easier for a beginner to understand. Such understanding is required to maintain the system. However, this method is subject to "Feature Creep" which can necessitate a compete overhaul and/or replacement in the unseen future.

As 'a year of Turnover' I am referring to the commencement date in column "A" +12months. The difficulty I am having now is that the dates in row 1 on sheet 2 are text generated from the database. Where as when trying to calculate this 1 year annual total it would be easier if they were date formatted. This can be counteracted by using a Text formula, but that could get way too messy considering each person's commencement date will be different.

I now have the new monthly data being copied into the sheet 2 correctly, so that isn't a problem anymore. Just the turnover for each of the years..

Commencement date: 13/04/2011
This would result in figures from April 2011 - March 2012 would = Year 1 Turnover

^
Hope that simple example answers where you don't follow my explanation?

I am not very confident with SQL coding as it's so specific. Being given the information shown, how would you calculate the annual Turnover?

This is the newer updated Workbook.

10908
^
Date is picked up from sheet 1 ref: B1 and copied using macro into sheet 2

[SamT]

As I mentioned, my "office" computer is down, so I am changing the title of this thread to attract responses from more people about the date problem.

[aideycorc]

Thank you! I shall await further response. Thanks for your help!