[sims] Sims Report in Word/Excel changes date format

[Chunks_]

Hi All,

 

Anyone else had this problem. Where you have a report in Sims it aims to give for instance, forename, surname and dob. You look at the preview and its all looks fine the students date of birth is in the format of something like 27/09/1996 yet when it gets put into work or excel (Office 2010) it puts it in the format 27 September 1996.

 

Anyone know how to stop it doing this ? it may just be a word/excel setting but i cant find it ...

 

Edit : I found this for excel > Options -> Advanced -> (check box on) Transition Formula Entrys (all the way down at the bottom). however that does actuive sheet and not the ones that come out of sims.... although this option doesnt exist in word.

 

Cheers

Edited September 27, 2011 by Chunks_
Additional Information

[vikpaw]

I got the same, except i can't even create a Word output due to some weird permissions issue! Excel output is a text field. preview must be configured differently.

If you send it to RTF format there is a check box to select numeric dates, which fixes it, though is not as usable as Excel.

Not sure about how to fix in Excel, and converting to a date will probably need at least two nested formulas) and is just a PITA if you have to convert manually. You could probably copy paste the rtf into Excel though, or get a macro to do it.

Macros are enabled in Excel and Office right?

[Alis_Klar]

Argh! Getting this problem too. This should be reported to Capita ASAP! :mad: @vikpaw thanks for the workaround.

 

:EDIT: Calmed down now i got @vikpaw 's workaround using RTF's to work. Was able to paste from RTF table open in word the whole column of 700 kids dates of birth into excel. Worked first time. Luckilly I had already formatted the cells to a date format with slashes rather than dashs and excel converted the date format for me.

Edited July 27, 2012 by Alis_Klar

[Alis_Klar]

This is another work around provided by Birmingham LA's Link2ICT using the excel convert text to columns feature

Thank you for your query. 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.

Note leave only TAB tick box (default) checked In step 2 of the wizard

 

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

 

Why does SIMS export an excel file with a date column set as text format anyway. If it was set to a date field this issue would not exist. They could default the output to a longer date format to avoid date month ambiguity also. Did try to get Link2ICT to report this as a bug to Capita but not sure if they will.

Edited August 10, 2012 by Alis_Klar

[matt40k]

It's a Microsoft bug, Capita just uses the standard tools, unfortunately Microsoft thinks it knows best and tries to figure out what the date field should be. I've had the same (ish) issue importing from .csv with it losing it's prefixing zero's.

[Alis_Klar]

Why does SIMS need scripting in office to be enabled if it is simply pasting flat text into fields. Agree with you on the leading zero issue. Mighty annoying!

[matt40k]

Just the way it was designed. Remember it hasn't changed that much since it was first done. I think they did look at Microsoft Reporting service but that was too overly complicated compared to what they've create. I assume the main reason they don't throw a bit of developer time at creating workarounds and such is that people would start asking for support for alternatives to MS Office and to be fair to the Capita, they've a Microsoft based software product - after all, SIMS's market place is 99% Microsoft based.

[Alis_Klar]

Getting a bit of topic here but...

I've only in the last 2 months moved over to SIMS (from Facility CMIS) and if 99% of their market are using Microsoft Office on Windows then only an ignorable 1% will be asking for support for other platforms surely. Sorry to argue but that was too tempting!

[matt40k]

Sure more then 1% will be asking

 

It is worth logging with Capita anyway, it's not a difficult thing to fix. It just takes time to test.

[round2it]

Might not be related but we had a similar problem with FMs reports the date formats were all messed up.

 

I pulled the registry settings for date format location etc from a known correct machine and imported them to faulty machine.

 

This fixed the problem for me

[vikpaw]

If you want to send the data straight to Excel, you can also just whack a formula in an adjacent column (not sure why i didn't try this before, or it didn't work, but it does now!) :

 

=DATEVALUE(C2)

 

and it should convert to a date.

 

It's just a faff if you want it in Word. I'd use RTF with numeric dates tick box if you want data in Word, but for Excel, you don't need the intermediate step, i'd just use the formula.

 

The reason it goes to text, i think is also because the data is formulated first into a text file. Certainly in the case of Excel output, the Macro in the report definition, will pull out data from the SQL, and dump as text to a text file, and then this is parsed and open by Excel. Which is why you see the first Excel worksheet close and then another open up with your data. It's a crazy way for it to work, but it does. I'm guessing the use of a text file means it's easier to manage the data, but you would get issues with number / numeric fields being opening in Excel and losing formatting, so having it verbose is quite useful.

[tben2505]

Hi All,

 

Anyone else had this problem. Where you have a report in Sims it aims to give for instance, forename, surname and dob. You look at the preview and its all looks fine the students date of birth is in the format of something like 27/09/1996 yet when it gets put into work or excel (Office 2010) it puts it in the format 27 September 1996.

 

Anyone know how to stop it doing this ? it may just be a word/excel setting but i cant find it ...

 

Edit : I found this for excel > Options -> Advanced -> (check box on) Transition Formula Entrys (all the way down at the bottom). however that does actuive sheet and not the ones that come out of sims.... although this option doesnt exist in word.

 

Cheers

 

This is annoying but it's easy to fix, just select the column and replace the spaces with forward a forward slash " " to "/" then they convert to date automatically.

 

I came across this thread as I thought there might have been a fix as I want staff to run reports and filter by dates without having to do this themselves.

 

Cheers all

 

T