Converting a date to acceptable format in excel

Currently, I am using Smart Sparrow to collect data for the effectiveness of using adaptive learning platforms to improve the pre-class experience of flipped learning.

Amongst the data, I want to collect is when a student completes the lesson.  If the student is completing the lesson too close to the deadline, his/her data needs to be interpreted differently than the one from who does it within a reasonable time frame.  The latter would help me to better address how the lesson can be improved as a student.  A student racing against the deadline would give unreliable data on how a lesson can be improved.

Smart Sparrow does track the time when a student completes the lesson but the format is as follows.

Dayth Month Year, Hour:Minute AM/PM format (e.g. 7th Sep 2017, 9:40am).

The data is downloadable as in a CSV format.  Unfortunately, they do not give day/time since epoch or in acceptable excel format to find the time difference between when the lesson was completed and the lesson deadline.  So I wrote a VBA module function ‘changedate’ to do this.  Here it is.

function changedate (DateTimeOriginal)
‘This program converts a date/time given in XXth Month Year, Time format
‘Example 19th Sep 2017, 9:40 am
‘to be converted to
’09/19/2017 9:40am
‘to acceptable excel format to subtract from other dates
‘declaring a 12 element long vector for months
Dim monthvector(11) As String
monthvector(0) = “JAN”
monthvector(1) = “FEB”
monthvector(2) = “MAR”
monthvector(3) = “APR”
monthvector(4) = “MAY”
monthvector(5) = “JUN”
monthvector(6) = “JUL”
monthvector(7) = “AUG”
monthvector(8) = “SEP”
monthvector(9) = “OCT”
monthvector(10) = “NOV”
monthvector(11) = “DEC”
‘Uppercasing the date for accurate comparison
DateTimeOriginal = UCase(Trim(DateTimeOriginal))
‘Length of input date

‘Adding a 0 in front if day of month is between 1-9 so that the
‘length of string is same for all dates
If IsNumeric(Mid(DateTimeOriginal, 2, 1)) = False Then DateTimeOriginal = “0” + DateTimeOriginal

‘Capturing Day Number
daynumber = Mid(DateTimeOriginal, 1, 2)

‘Capturing the Year
yearnumber = Mid(DateTimeOriginal, 10, 4)

‘Capturing the Month
For i = 1 To 12
If Mid(DateTimeOriginal, 6, 3) = (monthvector(i – 1)) Then
monthnumber = Str(i)
End If

‘Capturing time of day
timenumber = Right(DateTimeOriginal, 8)

‘Writing the date in acceptable format
changedate = monthnumber + “/” + daynumber + “/” + yearnumber + ” ” + timenumber
changedate = Trim(changedate)
End Function


Leave a Reply