Credit to Centauri Soldier as this function uses the String.ToTable function from his ABI plugin.
So I got bored this week and was working on a few things, and decided to tackle the DateDiff function. I know there are a few solutions kicking around but I was just curious to see if I could make it work.
I took the same approach as Excel, and I covert the date to a datecode then just find the difference between the two.
It's far from perfect but I think it will work for a lot of people.
It will work with the following formats:
M/D/YY
MM/DD/YYYY
M-D-YY
MM-DD-YYYY
Month D, YY
DayOfWeek, Month D, YY
So basically it works with any of the US formats (sorry my Euro and Canadian friends)
The biggest problem you run into is if you give it one date with a YY and the other with a YYYY format. (It treats "14" as the year 14AD which still works if you give it another 2 digit year, but will obviously give you an incorrect result when comparing to a date that is ~2000 years later)
The man reason I did it was so I could compare the result of File.GetAttributes().WriteDate to System.GetDate() without having to worry about formatting the date before I tried to manipulate it every time.
I didn't do exhaustive checking with it, but I compared all of the leap year scenarios I could think of and all of them agreed with the results in Excel.
So anyway, hopefully someone else might find it handy
So I got bored this week and was working on a few things, and decided to tackle the DateDiff function. I know there are a few solutions kicking around but I was just curious to see if I could make it work.
I took the same approach as Excel, and I covert the date to a datecode then just find the difference between the two.
It's far from perfect but I think it will work for a lot of people.
It will work with the following formats:
M/D/YY
MM/DD/YYYY
M-D-YY
MM-DD-YYYY
Month D, YY
DayOfWeek, Month D, YY
So basically it works with any of the US formats (sorry my Euro and Canadian friends)
The biggest problem you run into is if you give it one date with a YY and the other with a YYYY format. (It treats "14" as the year 14AD which still works if you give it another 2 digit year, but will obviously give you an incorrect result when comparing to a date that is ~2000 years later)
The man reason I did it was so I could compare the result of File.GetAttributes().WriteDate to System.GetDate() without having to worry about formatting the date before I tried to manipulate it every time.
I didn't do exhaustive checking with it, but I compared all of the leap year scenarios I could think of and all of them agreed with the results in Excel.
So anyway, hopefully someone else might find it handy
Code:
function DateDiff(sStartDate,sEndDate) local intFinal = 0 --Strip out DayOfWeek, and replace Month with a number sStartDate = String.Replace(sStartDate, "Monday, ", "", false); sStartDate = String.Replace(sStartDate, "Tuesday, ", "", false); sStartDate = String.Replace(sStartDate, "Wednesday, ", "", false); sStartDate = String.Replace(sStartDate, "Thursday, ", "", false); sStartDate = String.Replace(sStartDate, "Friday, ", "", false); sStartDate = String.Replace(sStartDate, "Saturday, ", "", false); sStartDate = String.Replace(sStartDate, "Sunday ", "", false); sStartDate = String.Replace(sStartDate, "January ", "1, ", false); sStartDate = String.Replace(sStartDate, "February ", "2, ", false); sStartDate = String.Replace(sStartDate, "March ", "3, ", false); sStartDate = String.Replace(sStartDate, "April ", "4, ", false); sStartDate = String.Replace(sStartDate, "May ", "5, ", false); sStartDate = String.Replace(sStartDate, "June ", "6, ", false); sStartDate = String.Replace(sStartDate, "July ", "7, ", false); sStartDate = String.Replace(sStartDate, "August ", "8, ", false); sStartDate = String.Replace(sStartDate, "September ", "9, ", false); sStartDate = String.Replace(sStartDate, "October ", "10, ", false); sStartDate = String.Replace(sStartDate, "November ", "11, ", false); sStartDate = String.Replace(sStartDate, "December ", "12, ", false); sEndDate = String.Replace(sEndDate, "Monday, ", "", false); sEndDate = String.Replace(sEndDate, "Tuesday, ", "", false); sEndDate = String.Replace(sEndDate, "Wednesday, ", "", false); sEndDate = String.Replace(sEndDate, "Thursday, ", "", false); sEndDate = String.Replace(sEndDate, "Friday, ", "", false); sEndDate = String.Replace(sEndDate, "Saturday, ", "", false); sEndDate = String.Replace(sEndDate, "Sunday, ", "", false); sEndDate = String.Replace(sEndDate, "January ", "1, ", false); sEndDate = String.Replace(sEndDate, "February ", "2, ", false); sEndDate = String.Replace(sEndDate, "March ", "3, ", false); sEndDate = String.Replace(sEndDate, "April ", "4, ", false); sEndDate = String.Replace(sEndDate, "May ", "5, ", false); sEndDate = String.Replace(sEndDate, "June ", "6, ", false); sEndDate = String.Replace(sEndDate, "July ", "7, ", false); sEndDate = String.Replace(sEndDate, "August ", "8, ", false); sEndDate = String.Replace(sEndDate, "September ", "9, ", false); sEndDate = String.Replace(sEndDate, "October ", "10, ", false); sEndDate = String.Replace(sEndDate, "November ", "11, ", false); sEndDate = String.Replace(sEndDate, "December ", "12, ", false); --Search for delimiter and convert string to table if String.Find(sStartDate, "/", 1, false) > 0 then tblStartDate = String.ToTable(sStartDate, "/") elseif String.Find(sStartDate, "-", 1, false) > 0 then tblStartDate = String.ToTable(sStartDate, "-") elseif String.Find(sStartDate, ", ", 1, false) > 0 then tblStartDate = String.ToTable(sStartDate, ", ") else intFinal = -1 end if String.Find(sEndDate, "/", 1, false) > 0 then tblEndDate = String.ToTable(sEndDate, "/") elseif String.Find(sEndDate, "-", 1, false) > 0 then tblEndDate = String.ToTable(sEndDate, "-") elseif String.Find(sEndDate, ", ", 1, false) > 0 then tblEndDate = String.ToTable(sEndDate, ", ") else intFinal = -1 end --convert strings to number values if #tblStartDate == 3 then tblStartDate[1] = String.ToNumber(tblStartDate[1]) tblStartDate[2] = String.ToNumber(tblStartDate[2]) tblStartDate[3] = String.ToNumber(tblStartDate[3]) else intFinal = -1 end if #tblEndDate == 3 then tblEndDate[1] = String.ToNumber(tblEndDate[1]) tblEndDate[2] = String.ToNumber(tblEndDate[2]) tblEndDate[3] = String.ToNumber(tblEndDate[3]) else intFinal = -1 end --check to make sure numbers converted correctly if tblStartDate[1] == 0 or tblStartDate[2] == 0 or tblStartDate[3] == 0 then intFinal = -1 end if tblEndDate[1] == 0 or tblEndDate[2] == 0 or tblEndDate[3] == 0 then intFinal = -1 end --deal with all of the date math if intFinal == 0 then FnMon1 = tblStartDate[1] FnDay1 = tblStartDate[2] FnYear1 = tblStartDate[3] FnMon2 = tblEndDate[1] FnDay2 = tblEndDate[2] FnYear2 = tblEndDate[3] FnDateCode1 = Math.Floor(FnYear1*365.25)+1 if FnMon1 == 1 then FnDateCode1 = FnDateCode1 + FnDay1 elseif FnMon1 == 2 then FnDateCode1 = FnDateCode1 + 31 + FnDay1 elseif FnMon1 == 3 then FnDateCode1 = FnDateCode1 + 59 + FnDay1 elseif FnMon1 == 4 then FnDateCode1 = FnDateCode1 + 90 + FnDay1 elseif FnMon1 == 5 then FnDateCode1 = FnDateCode1 + 120 + FnDay1 elseif FnMon1 == 6 then FnDateCode1 = FnDateCode1 + 151 + FnDay1 elseif FnMon1 == 7 then FnDateCode1 = FnDateCode1 + 181 + FnDay1 elseif FnMon1 == 8 then FnDateCode1 = FnDateCode1 + 212 + FnDay1 elseif FnMon1 == 9 then FnDateCode1 = FnDateCode1 + 243 + FnDay1 elseif FnMon1 == 10 then FnDateCode1 = FnDateCode1 + 273 + FnDay1 elseif FnMon1 == 11 then FnDateCode1 = FnDateCode1 + 304 + FnDay1 elseif FnMon1 == 12 then FnDateCode1 = FnDateCode1 + 334 + FnDay1 end if Math.Mod(FnYear1, 4) == 0 and FnMon1 >= 3 then FnDateCode1 = FnDateCode1 + 1 end FnDateCode2 = Math.Floor(FnYear2*365.25)+1 if FnMon2 == 1 then FnDateCode2 = FnDateCode2 + FnDay2 elseif FnMon2 == 2 then FnDateCode2 = FnDateCode2 + 31 + FnDay2 elseif FnMon2 == 3 then FnDateCode2 = FnDateCode2 + 59 + FnDay2 elseif FnMon2 == 4 then FnDateCode2 = FnDateCode2 + 90 + FnDay2 elseif FnMon2 == 5 then FnDateCode2 = FnDateCode2 + 120 + FnDay2 elseif FnMon2 == 6 then FnDateCode2 = FnDateCode2 + 151 + FnDay2 elseif FnMon2 == 7 then FnDateCode2 = FnDateCode2 + 181 + FnDay2 elseif FnMon2 == 8 then FnDateCode2 = FnDateCode2 + 212 + FnDay2 elseif FnMon2 == 9 then FnDateCode2 = FnDateCode2 + 243 + FnDay2 elseif FnMon2 == 10 then FnDateCode2 = FnDateCode2 + 273 + FnDay2 elseif FnMon2 == 11 then FnDateCode2 = FnDateCode2 + 304 + FnDay2 elseif FnMon2 == 12 then FnDateCode2 = FnDateCode2 + 334 + FnDay2 end if Math.Mod(FnYear2, 4) == 0 and FnMon2 >= 3 then FnDateCode2 = FnDateCode2 + 1 end -- **finally** find the difference between the two dates intFinal = Math.Abs(FnDateCode1 - FnDateCode2) end return intFinal end
Comment