Determining the number of days in a month
Well havent tried either Excel or VBA , but the logic will go like this ,


Loop through the table 
Say the date is day/month/year , where day,month and year are variables
lets have an array of months which is [31,28,31,30,31,30,31,31,30,31,30,31]
if year is divisible by 4 or 400 , then months[1]= months[1] + 1
now just display months[month]


Here months is the array and month is the number of month present in the date , for more info in arrays for VBA you can look here .