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



Badges added to MOOC courses

In response to the MOOC students of the numerical methods courses, we have added badges to every module in the course. Simply answer the quiz questions at the end of each module and you will see a badge in your grades.  We are exploring how we can provide certificates for the course.

We have a two-part MOOC course on Numerical Methods

To enroll in the courses, avoid the upper given links as it may ask you for elusive “join code”. Simply go to Search for numerical methods. Click on the course of your choice. Click on enroll at bottom of screen. You will not need a “join code”.

You can also register at


This post is brought to you by

Covariance between residuals and predictor variable is zero for a linear regression model.

Prove that covariance between residuals and predictor (independent) variable is zero for a linear regression model.

The pdf file of this blog is also available for your viewing.

covariance error predictor variable zero Page 1covariance error predictor variable zero Page 2
The pdf file of this blog is also available for your viewing.


This post is brought to you by

Sum of the residuals for the linear regression model is zero.

Prove that the sum of the residuals for the linear regression model is zero.sum of residuals is zero Page 1sum of residuals is zero Page 2________________________________________________

This post is brought to you by

A MATHCOUNTS problem solution via abstraction



This post is brought to you by

Holistic Numerical Methods Open Course Ware:

the textbooks on

the Massive Open Online Course (MOOCs) available at

Unexpected zeros error in MATLAB in zeros function

YouTube Comment: This MATLAB program gives me an error.  W=2.4; L=3; Delta=0.6; i=(W./Delta)+2; j=(L./Delta); T=zeros(i,j); . When I write 0.1 for Delta, there is an error for zeros statement.

Answer: When writing a new program, avoid using the semicolon as it suppresses the output. Write each line separately in a .m file and run the mfile. That way you would have noticed that “i” is turning out to be a real number. It shows up as 26.0000 but if you use format long statement, you will see that you get 25.999999999999996. You can round(i) and round(j) to the nearest integer. The reason “i” turns out to be 25.999999999999996 is because of roundoff error, as numbers and calculations get represented in binary format.