Using Dates in SIMS Reporting
by, 4th January 2012 at 06:12 PM (2605 Views)
When you output dates in SIMS with the Reporting tool, the results will come out as text and cannot be converted into a proper date with the number format window. This is a fix used in the SIMS Standard Portrait template to prevent Excel from throwing a fit and entering the data incorrectly. You can modify the template and remove the fix but that is best left to advanced users, there is a simpler option.
Let's take a date in the format "1 March 2012" in cell C2. You can use the DATEVALUE formula in another column to convert it into a numerical format used by Excel to represent dates. To do this, you enter:
You might need to choose Formulas then Calculate Now as SIMS has a tendency to disable automatic calculation. The datecode will now be shown as a number, in this case 40969. You can now right click the cell and choose Format Cells to change this to the Date format and make it readable. It will now work in Excel like any other date, including other formulae using dates or for grouping them in PivotTables.Code:=DATEVALUE(C2)
But wait, deleting column C with the SIMS output in it will break the formula as the cells don't contain the value as seen. The cell actually contains the formula and it is pointing to the cell in Column C which will throw an error when that cell is deleted. What do you do now? Paste Special is your friend here.
Before deleting Column C, copy the cell containing your DATEVALUE formula then paste it over itself by right clicking the cell and choosing Paste Special. Select the option called Values and click OK. The cell will now be stripped of it's formula and the result will be inserted into the cell, leaving it unaffected by the deletion of it's neighbouring Column C.
Total Trackbacks 0