+ Post New Thread
Results 1 to 11 of 11
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, ...
  1. #1

    Join Date
    Feb 2011
    Posts
    91
    Thank Post
    6
    Thanked 24 Times in 15 Posts
    Rep Power
    12

    Sims Report in Word/Excel changes date format

    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
    Last edited by Chunks_; 27th September 2011 at 11:50 AM. Reason: Additional Information

  2. #2

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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?

  3. Thanks to vikpaw from:

    Alis_Klar (27th July 2012)

  4. #3
    Alis_Klar's Avatar
    Join Date
    Oct 2007
    Location
    East Birmingham
    Posts
    287
    Thank Post
    142
    Thanked 20 Times in 10 Posts
    Rep Power
    17
    Argh! Getting this problem too. This should be reported to Capita ASAP! @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.
    Last edited by Alis_Klar; 27th July 2012 at 11:23 AM.

  5. #4
    Alis_Klar's Avatar
    Join Date
    Oct 2007
    Location
    East Birmingham
    Posts
    287
    Thank Post
    142
    Thanked 20 Times in 10 Posts
    Rep Power
    17
    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.
    Last edited by Alis_Klar; 10th August 2012 at 11:59 AM.

  6. #5

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,388
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158
    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.

  7. #6
    Alis_Klar's Avatar
    Join Date
    Oct 2007
    Location
    East Birmingham
    Posts
    287
    Thank Post
    142
    Thanked 20 Times in 10 Posts
    Rep Power
    17
    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!

  8. #7

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,388
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158
    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.

  9. #8
    Alis_Klar's Avatar
    Join Date
    Oct 2007
    Location
    East Birmingham
    Posts
    287
    Thank Post
    142
    Thanked 20 Times in 10 Posts
    Rep Power
    17
    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!

  10. #9

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,388
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158
    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.

  11. #10
    round2it's Avatar
    Join Date
    May 2009
    Location
    UK
    Posts
    992
    Thank Post
    193
    Thanked 143 Times in 101 Posts
    Rep Power
    36
    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

  12. #11

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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!) :

    Code:
    =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.

SHARE:
+ Post New Thread

Similar Threads

  1. [MS Office - 2010] Saving in Word and Excel 2010
    By stebo730 in forum Office Software
    Replies: 15
    Last Post: 16th November 2011, 09:56 AM
  2. Problem with date when merging excel data in word
    By CatherineMay in forum AV and Multimedia Related
    Replies: 1
    Last Post: 6th July 2011, 03:39 PM
  3. sims report excel autogrouping
    By sandeep2504 in forum MIS Systems
    Replies: 10
    Last Post: 26th January 2010, 07:33 AM
  4. Custom date format in SIMS Report
    By pod in forum MIS Systems
    Replies: 6
    Last Post: 2nd November 2009, 12:37 PM
  5. Replies: 8
    Last Post: 15th December 2008, 03:41 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •