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


Author: Autar Kaw

Autar Kaw ( is a Professor of Mechanical Engineering at the University of South Florida. He has been at USF since 1987, the same year in which he received his Ph. D. in Engineering Mechanics from Clemson University. He is a recipient of the 2012 U.S. Professor of the Year Award. With major funding from NSF, he is the principal and managing contributor in developing the multiple award-winning online open courseware for an undergraduate course in Numerical Methods. The OpenCourseWare ( annually receives 1,000,000+ page views, 1,000,000+ views of the YouTube audiovisual lectures, and 150,000+ page views at the NumericalMethodsGuy blog. His current research interests include engineering education research methods, adaptive learning, open courseware, massive open online courses, flipped classrooms, and learning strategies. He has written four textbooks and 80 refereed technical papers, and his opinion editorials have appeared in the St. Petersburg Times and Tampa Tribune.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: