Converting Text Dates to a Numeric Dates

Converting Text Dates to Numeric Dates

Sometimes when you import data, particularly dates,  Excel treats the data as text. You can use the DATEVALUE() function to convert text dates to real numeric dates.
 DATEVALUE() returns the serial number of the selected date. Once you have the serial date, you can format it to display as a date.

In this example, the data in Column A are dates that Excel treats as text. (A good way to tell if Excel is treating something as a number or text is to check the alignment. Text is left aligned whereas numbers are left aligned.)  In column B2, I used the formula =DATEVALUE(A2) to change the text in cell A2 into a serial number which displays as 39894.

39894 is the serial number of the date in cell A2.



To convert it to something meaningful, you just need to go the Number group  on the Home Tab

and click on the drop-down arrow beside General



In this particular example, I then selected the short date

The result is shown below:

DATEVALUE() only works on text dates. If you try it on a column of text and numeric dates, you will get a #VALUE error on the entries that are numeric.

LEN

I was getting my U.S. Christmas mailing list in order and noticed a problem on some of my zipcodes. Many of New England's zip codes begin with a zero so when I pulled my addresses into Excel to sort and filter them, Excel dropped the 0 from the beginning of the zipcode so for example, instead of 02186, Excel displayed 2186.
(It did this because it assumed the ZipCode was a number instead of text.)

The fix is easy.

In this case, I clicked in cell I2  and entered =IF(LEN(H2)=5,H2,"0"&H2)

LEN() counts the number of characters. So, I told Excel -if the number of characters in cell H2 total 5 then just display the contents of cell H2. However, if the number of characters in H2 do not total 5 then include a 0 at the beginning of the H2 cell contents. I used an ampersand (&) to tell Excel to join the 0 and the zipcode together  however you could also have used the Concatenate() function- I just think the & is faster.  Both LEN() and Concatenate() are Text functions.

Once I copied the formula down, I then selected all the zipcodes in Column I, copied them and then did Paste Values so that the formulas were overwritten with the actual numbers. I did that so that when I deleted Column H, my new zipcodes would display.

An alternative way would be to just hide Column H. 

 
This  IF assumed residential US zipcodes. If you had business zip codes which would have more than 5 characters or were including European postal codes then you would have to change the IF a bit.

Freezing Columns In an Access Table

You can FREEZE one or more of the columns in an Access table so that they become the leftmost columns and are visible at all times no matter where you scroll.

 

• Open a table in Datasheet view.
• Select the columns you want to freeze by clicking the field selector (top of the column) for that column
•  To select more than one column, click the column field selector and then, without releasing the mouse button, drag to extend the selection.
• Right-click and select Freeze Columns

Access handles this a bit differently from Excel - what it does is literally move that column so that it becomes the left most column in the table.

• To unfreeze a column, Right-click on the  frozen column heading and select Unfreeze Columns

And of course, Access does NOT move the column back to its original position - you have to move it manually.

Moving Individual Fields (Controls & Labels) in Access 2007

Moving Individual Fields in an Access 2007 Form




Moving fields in Access used to be pretty simple - NOT ANYMORE. Microsoft has added a Control Layout feature so if you want to move controls and /or labels separately then you need to become friends with the Remove icon located on the contextual Arrange tab in the Control Layout group!  The default is that all the controls and labels move together as a unit.
Oh and make sure you are in Design View- not the new Layout View they added.

In Access 2007, if you want to move an individual field it requires an additional step as the default is for all of the fields to move together.

• In Design view, select the control and/or label you wish to move
• Click on the Remove icon - found on the contextual Arrange tab on the Control Layout group
• Now you can move the individual control and/or label.

Spreadsheet Controls under Sarbanes-Oxley Section 404

This is based on an EBook Joe Helstrom, CPA wrote for CPASelfstudy.com entitled Spreadsheet Controls Under Sarbanes-Oxley Section 404

Spreadsheet Controls 

Spreadsheets have become pervasive in most companies and have many uses. Those that are used in the financial reporting process are of most concern to the assessment of the effectiveness of internal controls over financial reporting mandated by Sarbanes-Oxley. Several steps are recommended to accomplish the needed assessment related to spreadsheets. The first would be to get a handle on the population of spreadsheets that are used in the company. Secondly, determine whether the spreadsheet is used in the financial reporting process. Next, identify risk factors of the spreadsheet and grade the overall risk. Next, identify any compensating controls that reduce or mitigate the identified risks. Lastly, determine what remediation steps are necessary, if any, for the identified spreadsheets.

 

It should be noted that a high risk spreadsheet used in financial reporting, even with compensating controls, may not be able to achieve an adequate level of control in a spreadsheet environment. It may be necessary to migrate the process to an application where the information technology controls are developed and maintained by the information technology function.

 

Inventory all spreadsheets
The beginning of the “top down” approach would be to identify all spreadsheets used by the organization in the financial reporting process. This would include financial reporting, plant accounting, treasury, tax and operations. This can be done at the department level by asking each department head or supervisor to create a list of all spreadsheets used with the following information:

 

• Spreadsheet name
• Location of the spreadsheet file
• Department using the spreadsheet
• Description of spreadsheet purpose
• Spreadsheet users that have access

 

In addition to the above, the IT staff can be enlisted to query the company’s networks for spreadsheet files. This will help ensure that the inventory is complete.

 

Determine how current spreadsheets are being used

Once the spreadsheet inventory has been completed, an assessment of spreadsheet use must be performed. The first step is to segregate the spreadsheets into categories. These categories may include financial, operational and analytical.

 

The spreadsheets that fall into the “financial” category will carry the most risk potential. These will include spreadsheets that:

 

• Validate account balances

• Support transactions or journal entries

• Compute financial statement disclosures

• Perform financial reporting controls

 

Operational and analytical spreadsheets may also be important depending on the organization. However, these generally are used for operational decisions rather than in the financial reporting process.

 

Determine the risk factors of the spreadsheet

The financial spreadsheets (as well as any others that are significant to the financial reporting process), must be assessed for risk. Risk factors will include:

 

• The use of the spreadsheet and the use of the spreadsheet output

• Materiality of the affected account balance or disclosure

• Potential errors in downloaded data such as an incomplete download or a download of incorrect  data.

• Whether the spreadsheet uses complex calculations, formulas or macros.

• Number of individuals using the spreadsheet

• Size of the spreadsheet

• How well the spreadsheet is documented

  

These risk factors must be assessed along with the use of the spreadsheet and a risk rating should be assigned. As an example, a spreadsheet that is used for financial reporting disclosure, uses downloaded data, contains complex calculations and is used by a number of people would have a high risk rating. A similar spreadsheet used solely for analytical purposes would likely carry a moderate to low risk. Additionally, a spreadsheet that is used for a key financial control would likely carry a higher risk rating than one that is used to provide a list of documents. This is a subjective determination. It must be well documented so that a reviewer can assess the conclusions drawn by the company.

 

Evaluate compensating controls for risk factors

Certain organizations have already put controls in place to reduce the risk of material financial reporting error related to spreadsheets. These controls must be evaluated in light of the risk factors noted above and, once again, a determination must be made as to the effectiveness of the compensating controls. Compensating controls may include:

 

• Downloaded data has control totals that are compared the source data and validated by the user.

• If applicable, control totals or logic controls are used to validate user input.

• A logic inspection of the spreadsheet by an independent party is performed and documented prior to spreadsheet use.

• Spreadsheets are protected against unauthorized changes.

• Spreadsheet versions are used and, before a new version is utilized, it is tested and approved.

• Access to the spreadsheet is limited to authorized users via network access limitations and/or use of spreadsheet passwords.

• Spreadsheet documentation is adequate and up to date.

 

Once again, a determination must be made as to the adequacy of compensating controls. This can be a grade of either “Good”, “Moderate” or “Ineffective”. It is also a subjective determination.

 

Documentation of procedures

The spreadsheet inventory, description of use, risks and compensating controls should be summarized in a spreadsheet or workpaper. The documentation should also include your risk and control grades as well as a testing strategy for those spreadsheets that are deemed to have adequate compensating controls. Keep in mind that once a control has been identified, it still must be tested.

 

Remediation

For those spreadsheets whose compensating controls are moderate to ineffective, there should be changes made to enhance the compensating controls. Excel supports many compensating controls.

 

Keep in mind that a spreadsheet may not be appropriate for high risk accounts. In cases where the risk is high and the balance is material, migration to an application supported by information technology staff and control environment may be warranted.