The only way I've gotten the date/time format to play nice is to start with a report exported out of Canvas (such as a Courses CSV provisioning report), and then copy a date and paste it into the file you're planning to import. Of course, doing that is overwriting the data you have from Flatbridge with whatever arbitrary timestamp you copied. So you have to edit it to match the correct date. If you have a file with the same start/end dates, it's easy to tweak it once and copy the rest of the cells. But if you have a file with lots of different start and end dates, this isn't a great workaround.
Hello, @phil0150 !
I wanted to reach out and see how things are going with your date formatting/manual SIS import. It looks like @tdelillo gave you some things to try on how to get the date/time to format correctly for you - did that work for you?
I've gone ahead and shared this with https://community.canvaslms.com/community/answers/sis?sr=search&searchId=1bfbdcb9-224d-4b4d-a9fc-358..., https://community.canvaslms.com/community/answers/sis/k12-sis?sr=search&searchId=1bfbdcb9-224d-4b4d-..., and https://community.canvaslms.com/community/answers/sis/higher-education-sis?sr=search&searchId=1bfbdc... to try and get some additional eyes on your question. For the time being given the time between your question and now, I am going to mark this question as "Assumed Answered". If you'd kindly update us on where things stand we'd love to hear from you!
I must be missing something. I don't have a CSV file created by another program with the wrong dates, so I made my own CSV file with a date of 09/25/2017. When I opened it in Excel, it came through as midnight on that date.
Then I went in an set a custom date format of yyyy-mm-ddThh:mm:ssZ and saved the CSV file. When I opened it up, it kept the right date. The only thing I noticed different in yours was probably just a typo, but you had a space between the date and the T in the time and there is none. That's why I say I'm probably missing something.
Note that when I reopen the file in Excel, that value is no longer recognized as a date, it's now text.
Also note that the midnight that is assumed by Excel since it was only supplied a date is not accurate because your local timezone is not UTC. If you're in CDT, then it's 5 hours behind UTC, so midnight localtime on 9/25/2017 is actually 2017-09-25T05:00:00Z.
That may not be a solution since I'm unable to duplicate the problem right now.
In the past, I have had problems with certain features not being available when I open a CSV file. There are certain formatting settings (and possibly functions) that it just will not allow when I open an existing CSV file. What I do in cases like that is the reverse of what Tracey recommended. I create a new worksheet and then copy the contents of the CSV file into that empty one and close out the CSV file. Then I can do whatever I want, including setting the formatting on the dates, and save it.
Another possibility may be to import the CSV file into Excel rather than opening it directly. I don't do this myself, so I'm not sure if it would fix the issue or not.
For testing, until you get the process figured out, I would save it first as an Excel file and then as a CSV file. This can also be beneficial in the future because then you can open the Excel version back up and paste in the new data as values only, leaving the existing formatting, which would be in the proper format.