Numerical Dates in SIMS

[adamf]

Something raised by our office staff today, they have created a report to produce of a list of when staff CRB checks expire. When they enter this information they enter the date in the format dd/mm/yy (as with most things in SIMS)

When the report is produced, dates are coming out as 9th Januaray 2008 for example. When you preview the report at the design stage the date appear correctly, but not in the final output to Excel.

Has anybody come across this before and have we missed something stupid??!?

[Cache]

What's the cell formatted as in excel would be my first guess? Sounds a little bit like excel is just converting it into another date format, rather then leaving it as the original for whatever reason.

[adamf]

I thought that, but the report is generated and they automatically appear like that. If you select a cell then goto Format -> Cells -> Date/Time and select dd/mm/yy format click ok, it doens't change.

[tw15ns]

I haven't got SIMs in front of me at the moment but I seem to remember that dates are exported in some kind of text format to Excel (they are left aligned in cells rather than the normal right aligned). To convert these text dates to serial numbers use the =DATEVALUE(cell ref of date) function in a new column. This can then be formatted to display date how you want it.

I think this was how I got round it.

[adamf]

Just tested it and that works. Ok for a techie, long winded for a member of office staff!

[tw15ns]

Another possible solution would be to run a RTF report and make sure that the Show Numeric dates is checked. You can then copy the output of this report to Excel and format the dates as required. Still not perfect but better than the above.

[Greg]

It's a known 'feature'

KB67226 - Unable to format dates in Excel reports

--------------------------------------------------------------------------------
Created 06/03/2007
Last Updated 05/11/2008


--------------------------------------------------------------------------------
Affected Products SIMS .net Reporting

--------------------------------------------------------------------------------

Notes Sims .net Reporting


Unable to format dates in Excel reports


When report is run the date of birth will show
i.e 21st January 1994 would like the format to be 21/01/04
--------------------------------------------------------------------------------

Advice On investigation it was observed that, in order to avoid ambiguity in dates, the application specifically formats the date time to long date while exporting to Excel.


To format the date in the report as required after export to excel, please follow the below mentioned steps:


1) select the 'DOB' column in the excel report output.


2) Go to Menu Data |Text to Columns. A wizard will appear for the same.


3) Select 'Delimited' in Step 1 click 'Next', click 'Next' in Step 2. In Step 3, select the 'Date:' radio button. select 'DMY' from the drop down. click on Finish. The date will be formatted in the short date format.


4) Now go to Menu | Format | Cells | Date | Select the format like (dd/mm/yy) and click on 'Ok' | Date will be changed accordingly