MIS Systems Thread, Sims Report in Word/Excel changes date format in Technical; Hi All,
Anyone else had this problem. Where you have a report in Sims it aims to give for instance, ...
27th September 2011, 11:19 AM #1
- Rep Power
Sims Report in Word/Excel changes date format
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.
Last edited by Chunks_; 27th September 2011 at 11:50 AM.
Reason: Additional Information
27th September 2011, 02:37 PM #2
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?
Thanks to vikpaw from:
Alis_Klar (27th July 2012)
27th July 2012, 11:13 AM #3
- Rep Power
10th August 2012, 11:05 AM #4
- Rep Power
This is another work around provided by Birmingham LA's Link2ICT using the excel convert text to columns feature
Note leave only TAB tick box (default) checked In step 2 of the wizard
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.
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.
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
Last edited by Alis_Klar; 10th August 2012 at 11:59 AM.
10th August 2012, 11:52 AM #5
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.
10th August 2012, 11:58 AM #6
- Rep Power
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!
10th August 2012, 12:19 PM #7
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.
10th August 2012, 12:58 PM #8
- Rep Power
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!
10th August 2012, 01:05 PM #9
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.
10th August 2012, 07:02 PM #10
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
25th August 2012, 12:39 PM #11
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!) :
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.
By stebo730 in forum Office Software
Last Post: 16th November 2011, 09:56 AM
By CatherineMay in forum AV and Multimedia Related
Last Post: 6th July 2011, 03:39 PM
By sandeep2504 in forum MIS Systems
Last Post: 26th January 2010, 07:33 AM
By pod in forum MIS Systems
Last Post: 2nd November 2009, 12:37 PM
By kerlj001 in forum MIS Systems
Last Post: 15th December 2008, 03:41 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)