Unanswered: Wrong Answer on Difference in Two Dates
I am attempting to troubleshoot some problems that I am having with part of my program. After stepping line-by-line through the program, I see that my logic is correct but Brilliant Database is giving the wrong answer when one date is subtracted from another??? In the first example that I am seeing this error (which is also the first date calculation), I am simply subtracting one date from another - 04/13/2013 - 03/12/2012. The difference is 397 days though BD gives an answer of 387. I have a variable assigned to each date (i.e., date_difference = adjusted_date - completion_date). One thing I notice is that the first date is in a format like '2013-04-13' while the second date is in a format like '03/12/2012'. The first date comes from the system 'dateCurrent()+25 (the +25 just represents 25 days in the future) while the second date come from a calender Input Box. I have searched the threads here and see nothing that concerns calculations regarding dates. Why am I seeing erroneous calculations with with dates? What can I do to fix this?
It is certainly not working for me. I tried a different approach so that all things are on the same level. The dates are now appearing in a numerical format (for example, today's date of 03/20/2013 appears as 41353). I am comparing this value to another value (assume it is an earlier date from last week, 03/15/2013, which appears as 41348). When I use an 'If' statement to see if variable 2 (41348) is greater than the variable 1 (41353), the program follows the logic of the statement being true when it is not. I wasted several hours with this yesterday and I am really wondering why I keep getting invalid answers???
It now works but this leaves me more confused. I tried a variety of methods and got inconsistent results. I have always worked with EXCEL and EXCEL is very quirky concerning any calculations with dates. I guess I will have to re-learn how to do this in Brilliant Database too since it is not as straightforward as I feel it should be!!!