Downloading Report with Users and User Profile Fields – Dates displayed wrongfully and customization needed!

Downloading Report with Users and User Profile Fields – Dates displayed wrongfully and customization needed!

by Zhivko Z -
Number of replies: 7

Hi,

Is there a report, which would give me Students Data from the User Profile Fields?

I ran out one from Site Administration – Users – Accounts – Bulk User Actions – Choose users – Download. However:

1.       The dates (DOB and all others) from Users Fields come is a weird format on the downloaded Excel sheet, and they are not correct! For example, a DOB would looks like: 1478415600. How can I fix this??

2.       How can I customize this report to include only the User Profile fields I need? Or at least to exclude the empty fields such as Skype, ICQ, Yahoo, MSN, phone 2, etc.?

3.       Is there a way to add to this report student registration and course completion date?

Thanks

 

Moodle 3.1.2


Average of ratings: -
In reply to Zhivko Z

Re: Downloading Report with Users and User Profile Fields – Dates displayed wrongfully and customization needed!

by Emma Richardson -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers

Once in Excel, it would be easy to just delete the columns you don't want.  The number format is in UNIX time, you just need to convert to regular date format - you can google how to do that.

However, if you want more complex reporting, I recommend downloading the Configurable reports plugin.  Then search through the reports that others have created until you find something close and edit as needed.  You will need to know SQL.

Average of ratings: Useful (1)
In reply to Emma Richardson

Re: Downloading Report with Users and User Profile Fields – Dates displayed wrongfully and customization needed!

by Zhivko Z -

Thank you very much, Emma!

Is there any way to setup Moodle to use a regular time format instead of UNIX for this report?? For example - the Course Completion reports do come with normal dates.

Average of ratings: -
In reply to Zhivko Z

Re: Downloading Report with Users and User Profile Fields – Dates displayed wrongfully and customization needed!

by AL Rachels -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

Hi Zhivko,

Once you download the report, how about just format the column for dates and let Excel change them from UNIX time to something you want? he last time I used Excel it had a number of different possible looks for the resulting format.

Average of ratings: Useful (1)
In reply to AL Rachels

Re: Downloading Report with Users and User Profile Fields – Dates displayed wrongfully and customization needed!

by Zhivko Z -

Hello Al,

Do you mean - just formatting the cells to date format (image attached)?

I tried that with Excel 2010 and 2016 - there is no UNIX option. Tried all the listed options for Date and Time as well.

Thanks

Attachment Excel Date Format.PNG
Average of ratings: -
In reply to Zhivko Z

Re: Downloading Report with Users and User Profile Fields – Dates displayed wrongfully and customization needed!

by Just H -

You need to use a formula; see here for example.

Average of ratings: Useful (1)
In reply to Zhivko Z

Re: Downloading Report with Users and User Profile Fields – Dates displayed wrongfully and customization needed!

by AL Rachels -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

Hi Zhivko,

I've been really ill the past couple of days and didn't make myself clear as I thought I was doing. As Just H mentions it actually needs to be done with a formula and format.

Here are three possibilities in which you can see the formula, except for the cell address is the same, but each result used a different date format:


Example
Unix time
 Formula Result
1   1378677600 
  =(T2/86400)+25569+(-5/24) 
  09/08/2013 17:00:00 
2   222130800   =(Y2/86400)+25569+(-5/24)   January 14, 1977
3   1378677600
   =(AC2/86400)+25569+(-5/24)
   Sunday, September 8, 2013
Note that in the 3 formulas, T2, Y2, and AC2 are cell addresses which contained the Unix time number.

Average of ratings: Useful (1)
In reply to AL Rachels

Re: Downloading Report with Users and User Profile Fields – Dates displayed wrongfully and customization needed!

by Zhivko Z -

Hello Just H and Al,

Thanks for the information.

I was trying to find a way to just have the date normal when I pull a report. So, if I have to pull multiple reports - I won't need to add additional columns, formulas and calculations every time... But I guess formula is the only way...

Thank you!

Average of ratings: -