DD/MM/YY date format in excel files displaying as MM/DD/YY

[willmoin]

Oh I love problems like this.... not!

So one of our lecturers on our new install of Canvas was uploading a timetable in Excel format to her course and finds that the document viewer within Canvas is reinterpretting her file into MM/DD/YY format. So she's using Excel set for UK date format etc. saving it as a standard .xlsx file and uploading that to the Modules area of her course. She's finding that dates in her excel which she has entered and be understood by our students as DD/MM/YY, are then being displayed on Canvas as MM/DD/YY. Lecturer can obviously save and share a PDF in this case but it does present a bit of risk if a staff member wanted to deliberately share an Excel in the future and doesn't realise. It's also just a bit annoying that it is not customised to our region!

Me and colleagues have tested ourselves and found same issue. Tier 1 support came back and suggested something is wrong with our Excel setup.

Has anybody else had experience of this or can provide any advice?

Thanks, Will (Liverpool, UK)

Screenshot comparing Excel to Canvas's date formats

[willmoin]

This is not the solution but...

My mate from another uni has pointed out that this is something to do with the default date position in Excel being date/time formats being dynamic in that they update to the view settings of viewer. In our case our document viewer in Canvas must be referring to some locale setting somewhere that is not configured correctly, my guess at the point where a new file is processed for display by the document viewer rather than for the individual Canvas user (maybe on the server somewhere in the cloudImage showing potential fix in Excel?)

The solution I think we need is to find a way of updating the document viewer to reflect our institutions location - any ideas?

[gnoack]

Hi @willmoin 

Time Zone in Canvas is set at the account or course level, it is not a global setting. Which makes sense if teaching online students across time zones.  Depending on permissions at your Canvas instance, users can set their time zone in their profile settings, or it is set by the administrators when they create accounts. 

How do I edit a user's name, time zone, or email i... - Instructure Community (canvaslms.com)

How do I set a time zone in my user account as an ... - Instructure Community (canvaslms.com)

[willmoin]

Hi @gnoack 

Thanks for the suggestion and also links

However on testing it looks like we have the correct time zone already established as default in the course and user settings ("London (+00:00/+01:00)"). Just to confirm, this is in regard to documents uploaded into Canvas as content and rasterised/rendered by Canvas for it's in-line document Preview. At point of conversion it is making decisions about how to interpret date format and this does not seem to align to user or course timezone settings.

It's difficult for our Tier 1 support to verify because they have different setups but I have confirmed with other UK insitutions and the ones I have asked appear to have the same issue.

Thanks, Will

[gnoack]

Hi @willmoin 

I don't know if this will help, but it may be something you can show Tier 1 support. 

I tested this here in the states, I'm in Illinois.

I setup three columns, two with US Date format, one with the asterisk date and one with out.  Then one column for UK dates.  What was strange was when in Excel and I switched to the UK date format, I did not have the option to choose with or with out asterisk.  See image one. 

Image 2 shows the excel sheet. Image three shows the rendering in Canvas. 

This tells me that at least some instances of Canvas has the ability to render dates correctly from Excel files based on their cell formatting settings. It suggests, that there may be some difference in the version of Excel for US v UK. 

Image 1: no option to choose a date format with asterisk.

Image 2: excel data

Image 3: rendering of excel file in Canvas

 

[willmoin]

Thanks very much @gnoack again for looking at this! Very interesting. From my blurry eyes it looks like for you your input rendered correctly to the output. I might be missing something there and will need a relook next week. But what you noticed in Excel was interesting, when I looked to and selected US locale for the date I also did not see astrisk so maybe it's anything other than your OS locale then it takes away this option.

Testing out what you did with the US format, I found that Canvas did interpret the US date format as recorded in Excel (still gets UK stuff with the asterisk wrong though - which I think is the default)

What you found I think backs up what Tier 1 found on their own PC, which at the time I didn't think made much sense. In fact in a sense the Tier 1 person was correct in that all dates need to not have the asterisk, so before uploading an Excel to Canvas make sure any dates don't have one. If Preview could interpret correctly the asterisk (which I think is the default in Excel) for the customers server this would be better - unless I'm wrong!

Hope you have a good one when it arrives. a 9.2 IPA is calling my name

"I see that you are seeing the date format change on files when uploading them to Canvas. Taking a look a the example file that you sent to me, it looks like the format is m/d/y and when I am testing this in my own instance, the format of dates that are entered are left d/m/y when uploaded into Canvas. In this case I would recommend looking at how Excel is exporting those file to make sure it is exporting the files in the correct format." (Tier 1)