Date difference

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

If we find the date difference between two dates using datediff function, it
may end up an inaccurate difference. For example, the start date is 1 July,
2003 and the end date is 5 July, 2003. The difference is 5 days. The
answer may be right or wrong. If the start date is 1 July 2003 00:00 and
the end date is 6 July 2003 00:00. The accurate answer should be 3-5 days.
If we ask the staff to record the time of start date and end date, human
delay and error may happen too unless there is an aid of time recorder. I
would like to know the general perception of this issue.



Thanks,



Ray
 
Ray said:
If we find the date difference between two dates using datediff function, it
may end up an inaccurate difference. For example, the start date is 1 July,
2003 and the end date is 5 July, 2003. The difference is 5 days.
Click to expand...

4 days : 5-1 = 4

The
answer may be right or wrong. If the start date
Click to expand...

start [time]
is 1 July 2003 00:00 and
the end date
Click to expand...

end [time]
is 6 July 2003 00:00. The accurate answer should be 3-5 days.
Click to expand...

5 days: 6-1 = 5
 
See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)



Ray said:
If we find the date difference between two dates using datediff function, it
may end up an inaccurate difference. For example, the start date is 1 July,
2003 and the end date is 5 July, 2003. The difference is 5 days.
Click to expand...

DateDiff always gives the correct answers according to its definition: 5 - 1
= 4. It only looks at the "raw" date and disregards the time component.
Note that:

?DateDiff("d", #07/11/2003 23:59:59#, #07/12/2003 00:00:00#)
1

i.e. the answer is 1 day (correct according to DateDiff definition) even
though the 2 values are different by 1 second!

Also, you want to count both end dates and DateDiff doesn't.


The
answer may be right or wrong. If the start date is 1 July 2003 00:00 and
the end date is 6 July 2003 00:00. The accurate answer should be 3-5
Click to expand...
days.

The accurate answer is 5 days. 3 to 5 days is not an accurate answer. It
may be that your definition of the difference is not accurate. You need to
be precise on what you need and use the correct expressions / functions.


If we ask the staff to record the time of start date and end date, human
delay and error may happen too unless there is an aid of time recorder. I
would like to know the general perception of this issue.
Click to expand...
You can use the Date and Time Picker but if garbage comes in the database,
garbage will come out.
 
Van,

I appreciate and accept your explanations. I am not saying the problem of
Access function. this is not my point. It seems we all talk about database
calculation but ignore the calculation of normal people who have no idea
about Access functions. For example, if you send a clothe for cleaning on 1
July and get it back on 5 July. How many days for the cleaning if you are a
normal people (forget about function calculation)? If you need to produce a
report for the efficiency of cleaning to the customer, what is the
acceptable answer for normal people, especially to the customers?

Thanks,

Ray
 
Well I would say 4 days. Or if you want to calculate this minus holidays and
Saturdays and Sundays, then I might say 1 day (in the USA).

What rules do you want to apply for the calculation? If you define those, then
you can develop (or copy) a custom VBA function to give you what you want.
 
Back
Top