+ Post New Thread
Results 1 to 7 of 7
MIS Systems Thread, Numerical Dates in SIMS in Technical; Something raised by our office staff today, they have created a report to produce of a list of when staff ...
  1. #1

    Join Date
    Apr 2007
    Location
    Croydon
    Posts
    501
    Thank Post
    18
    Thanked 31 Times in 30 Posts
    Rep Power
    22

    Question Numerical Dates in SIMS

    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??!?

  2. #2
    Cache's Avatar
    Join Date
    Apr 2008
    Location
    Cumbria
    Posts
    1,225
    Thank Post
    455
    Thanked 177 Times in 174 Posts
    Blog Entries
    3
    Rep Power
    65

    Lightbulb

    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.

  3. #3

    Join Date
    Apr 2007
    Location
    Croydon
    Posts
    501
    Thank Post
    18
    Thanked 31 Times in 30 Posts
    Rep Power
    22
    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.

  4. #4

    Join Date
    Sep 2008
    Location
    Newcastle upon Tyne
    Posts
    17
    Thank Post
    0
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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.

  5. Thanks to tw15ns from:

    adamf (10th January 2009)

  6. #5

    Join Date
    Apr 2007
    Location
    Croydon
    Posts
    501
    Thank Post
    18
    Thanked 31 Times in 30 Posts
    Rep Power
    22
    Just tested it and that works. Ok for a techie, long winded for a member of office staff!

  7. #6

    Join Date
    Sep 2008
    Location
    Newcastle upon Tyne
    Posts
    17
    Thank Post
    0
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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.

  8. #7

    Join Date
    Jan 2007
    Posts
    100
    Thank Post
    0
    Thanked 34 Times in 23 Posts
    Rep Power
    21
    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

SHARE:
+ Post New Thread

Similar Threads

  1. create a folder with dates,
    By sharkster in forum Scripts
    Replies: 2
    Last Post: 3rd April 2008, 09:04 AM
  2. Dates in SIMS reports
    By cjohnsonuk in forum MIS Systems
    Replies: 0
    Last Post: 19th March 2008, 12:25 PM
  3. dates for admin pak
    By goodhead in forum Windows Vista
    Replies: 5
    Last Post: 7th March 2007, 04:42 PM
  4. BSF dates - are you prepared?
    By CyberNerd in forum BSF
    Replies: 29
    Last Post: 1st December 2006, 09:43 AM

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
  •