[MDL-45452] Completion report: Date format for "Excel-compatible format" CSV export updated to use excel compatible format - Moodle Tracker

XML

Word

Printable

Details

Type: Improvement
Status: Closed
Priority: Minor
Resolution: Fixed
Affects Version/s: 2.7
Fix Version/s: 4.3
Component/s: Course completion
Labels:
- ci
- patch
- release_notes
- triaged

Affected Branches:
MOODLE_27_STABLE
Fixed Branches:
MOODLE_403_STABLE
Pull from Repository:
https://github.com/leonstr/moodle
Pull Master Branch:
~~MDL-45452~~-course_completion_excel_dates
Pull Master Diff URL:
https://github.com/moodle/moodle/compare/master...leonstr:MDL-45452-course_completion_excel_dates
Testing Instructions:
Hide

In a course with some completions, click Reports > Course Completion.

With some completions showing, click "Download in Excel-compatible format (.csv)".

Open the file in Excel. Ignore any message about SYLK (that is unrelated to this issue).

In Excel, select a date field.

Click Format > Cells and see that date formats can be applied to the column.

Select a date column in the Excel worksheet.

Use Data > Sort to ensure Excel can now sort by the date column
Show
In a course with some completions, click Reports > Course Completion. With some completions showing, click "Download in Excel-compatible format (.csv)". Open the file in Excel. Ignore any message about SYLK (that is unrelated to this issue). In Excel, select a date field. Click Format > Cells and see that date formats can be applied to the column. Select a date column in the Excel worksheet. Use Data > Sort to ensure Excel can now sort by the date column

Description

At the bottom of the "Course completion" report are the following two links:

Download in spreadsheet format (UTF-8 .csv)
Download in Excel-compatible format (.csv)

Both currently output dates in whatever human-readable format is set as the user's locale preference for the site in general. These are (generally) not Excel-compatible, so Excel sees them as arbitrary strings rather than as dates, which can be sorted, reformatted, etc.

Specifically choosing "Excel-compatible" should instead output dates that Excel can recognize as such. ISO 8601 format, userdate('%Y-%m-%d %H:%M:%S'), fits the bill. The referenced commit does so.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

MDL-45452.310.patch
0.8 kB
13/Jul/21 7:07 PM
MDL-45452.png
536 kB
02/Aug/23 9:11 AM

Issue Links

Discovered while testing

MDL-45832 The activity completion report should be using the csv class.

Closed

MDL-46234 Switch course completion, activity completion from CSV to Microsoft Excel format

Closed

has been marked as being related by

MDL-39156 Microsoft Excel refuses to open CSV files

Closed

Activity

People

Assignee:: Leon Stringer

Reporter:: Ray Morris

Peer reviewer:: Paul Holden

Integrator:: Ferran Recio

Tester:: Ron Carl Alfon Yu

Participants:: Adrian Greeve, Ankit Agarwal, CiBoT, Damyon Wiese, Ferran Recio, Helen Foster, Leon Stringer, Michael de Raadt, Michael Lipp, Paul Holden, Phil Everist, Ray Morris, Ron Carl Alfon Yu, Sara Arjona (@sarjona)

Component watchers:: Amaia Anabitarte, Carlos Escobedo, Laurent David, Mikel Martín Corrales, Sabina Abellan, Sara Arjona (@sarjona)

Votes:: 12 Vote for this issue

Watchers:: 15 Start watching this issue

Dates

Created:: 08/May/14 7:05 AM

Updated:: Yesterday 1:24 PM

Resolved:: 04/Aug/23 8:17 PM

Fix Release Date:: 9/Oct/23

Time Tracking

Estimated:

Remaining:

Logged:

2h 2m