Urgent!!! Is DateDiff() Inclusive or exclusive

  • Thread starter Thread starter Saabster
  • Start date Start date
S

Saabster

I've got the following code in a report:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As
Integer)
dteStart = Forms!frmUtilizationRpt!txtStartDate
dteEnd = Forms!frmUtilizationRpt!txtEndDate
varDays = DateDiff("d", dteStart, dteEnd)
varDays = (varDays / 7) * 5
intNumHours = varDays * 8
lngUtil = Me.SumOfHours / intNumHours
Me.txtHrsInPeriod = intNumHours
Me.txtUtilPercent = lngUtil
End Sub

I'm wondering if the DateDiff() function will take into account the
starting and ending dates, or just the days between the 2 dates. For
example if I have dteStart as 01/01/2006 and dteEnd as 01/15/2006 I
would expect it to count either 15 days if it is inclusive or 13 days
if it is exclusive.

When I tested it, it counted 14 days, so which day is it loosing or
adding?

Thanks in advance
 
Saabster said:
I'm wondering if the DateDiff() function will take
into account the starting and ending dates, or just
the days between the 2 dates. For example if I have
dteStart as 01/01/2006 and dteEnd as 01/15/2006 I
would expect it to count either 15 days if it is
inclusive or 13 days if it is exclusive.
Click to expand...

I don't know why you would think DateDiff is counting, it is telling you the
difference in the chosen unit, obviously, in your case "days" between the
two dates. Try it with today's date and tomorrow's date (which I certainly
agree is one day later). Surely enough, it will return 1 day "difference".
If you'd like start with 1/1/2006 and execute DateDiff on end dates of
1/2/2006, 1/3/2006, . . . through 1/15/2006. You will find it follows a
logical progression... 1/2/2006 is one day after 1/1/2006, 1/3/2006 is 2
days after 1/1/2006, etc.
When I tested it, it counted 14 days, so which
day is it loosing or adding?
Click to expand...

As I can't see your facial expression, I have to ask, "Are you asking 'which
day is it loosing or adding' with a straight face?"

DateDiff is calculating, not counting, and it is telling you how many days
from "start" it is to "finish". That is, fourteen days from 01/01/2006 it
will be 01/15/2006.

Larry Linson
Microsoft Access MVP
 
Larry,

I understand what you are saying. so my question is how can I handle
this calculation?

I am trying to calculate a person's utilization based on time that they
have entered into the database.

The report prompts the user via a form for start date and end date.

I then need to figure out the number of hours available within that
time period.

And yes I was asking that with a straight face because I expected one
of two possible answers not the one I was getting. So how do I count
the number of days in a period?
 
Larry,

I understand what you are saying. so my question is how can I handle
this calculation?

I am trying to calculate a person's utilization based on time that they
have entered into the database.

The report prompts the user via a form for start date and end date.

I then need to figure out the number of hours available within that
time period.

And yes I was asking that with a straight face because I expected one
of two possible answers not the one I was getting. So how do I count
the number of days in a period?
Click to expand...

You logic is off because you expect to get the number of days between
the BEGINNING of the start date (let's say 1/1/2006) and the END of
the End date (1/15/2006).

If that is what you are looking for, then:

=DateDiff("d",[StartDate],[EndDate])+1

If you want work hours availability on the basis of an 8 hour workday,
then:

=(DateDiff("d",[StartDate],[EndDate])+1) * 8
 
If they enter a time as well as a date, you can take the difference
to get the number of hours.

(It will be the difference between the two times. 8 AM to 10 AM
will be 2 hours, not 1 or 3).

(david)
 
Just to add my own interpretation.

DateDiff counts the number of boundaries crossed or the number of times the
value you are checking changes. It does this much more efficiently than
actually looping from a to b and adding one increment (hour,day, month, year
etc) until it reaches or exceeds the cutoff.

If you are checking years with datediff, you will see that Dec 31 2005 to
Jan 3 2006 returns 1
If you are checking months with datediff, you will see that Dec 31 2005 to
Jan 3 2006 returns 1
If you are checking days with datediff, you will see that Dec 31 2005 to Jan
3 2006 returns 3

In answer to the original poster, it counts the number of intervals
necessary to change A to B.
 
Okay that is what I'm looking for Thanks. Now to put a better
explanation of what I'm trying to accomplish with the report.

The report is a utilization report for a time card application.

I'm prompting the user for a start date and end date to use as
parameters for the rpt.

I'm not subtracting holiday time, (since I don't have a full
holiday/calendar sysstem built in to this.

The query the report is based on selects all actual hours for all
employees except those where the project is considered non chargeable
hence not counted towards utilization.

I sum the actual hours by user for the given time period. So far so
good.

Now here is where I get hung up. I want to divide the chargeable hours
by the available hours in the same time period. Assuming 8 hrs per day
and a standard 5 day week that comes out to 40/week.

But since the report is not limted to pulling by week, and can in fact
be for any time period from a day to who knows.... How can I figure out
the # of available days in a time period if the time period spans 3.5
weeks?

I've got the following code, and I think it's a start but not quite
there yet.

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As
Integer)
dteStart = Forms!frmUtilizationRpt!txtStartDate
dteEnd = Forms!frmUtilizationRpt!txtEndDate
varDays = (DateDiff("d", dteStart, dteEnd) + 1
varDays = (varDays / 7) * 5
intNumHours = varDays * 8
lngUtil = Me.SumOfHours / intNumHours
Me.txtHrsInPeriod = intNumHours
Me.txtUtilPercent = lngUtil
End Sub

If anyone can help figure this out I'd be very appreciative.

Thanks

Craig



You logic is off because you expect to get the number of days between
the BEGINNING of the start date (let's say 1/1/2006) and the END of
the End date (1/15/2006).

If that is what you are looking for, then:

=DateDiff("d",[StartDate],[EndDate])+1

If you want work hours availability on the basis of an 8 hour workday,
then:

=(DateDiff("d",[StartDate],[EndDate])+1) * 8
Click to expand...
 
Back
Top