Counting the number of days between 2 dates starting from 1 not 0

[antheana]

Hi there,

I have created Job table where I have to enter the startdate and finish date for each job I work on. The problem is that some jobs only last 1 day. I have a field called JobLength where I am trying to calculate the numerical difference between the startdate and the enddate, but it always comes back with 0. If I work on a job starting on 17/11/06 and finishing on 17/11/06, how do I get it to say 1 (as in 1 day?).

I have tried DateDiff, when using the form:

=DateDiff("d",[JobStartDate],[JobFinishDate])

But it still comes up with one. Any ideas?

I am using MS Access 2003.

[Killer42]

Hi there,

I have created Job table where I have to enter the startdate and finish date for each job I work on. The problem is that some jobs only last 1 day. I have a field called JobLength where I am trying to calculate the numerical difference between the startdate and the enddate, but it always comes back with 0. If I work on a job starting on 17/11/06 and finishing on 17/11/06, how do I get it to say 1 (as in 1 day?).

I have tried DateDiff, when using the form:

=DateDiff("d",[JobStartDate],[JobFinishDate])

But it still comes up with one. Any ideas?

I am using MS Access 2003.

Two possibilities come to mind.

• Use IIF() to return one if they match, otherwise DateDiff.
• =DateDiff("d",[JobStartDate],[JobFinishDate])-([JobStartDate]=[JobFinishDate])

Note, True generally returns -1 which is why we subtract rather than add.

I suppose the problem with either of these approaches is, are the values the same or not? If this is a date only, they would be. If it includes time, they wouldn't.

[Killer42]

I suppose the problem with either of these approaches is, are the values the same or not? If this is a date only, they would be. If it includes time, they wouldn't.

On further reflection, it might be messy but you could probably overcome this problem (if it exists) by checking whether the returned value from DateDiff was zero, rather than whether the dates are the same. In other words

=DateDiff("d",[JobStartDate],[JobFinishDate])
  - (DateDiff("d",[JobStartDate],[JobFinishDate]) = 0)

Another possibility would be to add, say, 0.9 to the result then use the Int function to chop off the decimal places.

=Int(DateDiff("d",[JobStartDate],[JobFinishDate]) + 0.9)

I don't know whether any of these are any good, but they might give you some ideas to play with.

[Killer42]

Ok, scratch the Int idea - it won't work. I was thinking of DateDiff returning a decimal part of a day, or something.

Sorry about this folks, just brainstorming with myself, I guess.

Oh! And bumping up my message count, naturally. After all, that's what we're here for. :)

[MMcCarthy]

I think your problem is more basic than that. If you start a job on 17/11/06 and finish on 18/11/06, I assume you want to return 2 days but this will only return 1.

=DateDiff("d", #17/11/2006#, #18/11/2006#)

This should solve your problem:

=DateDiff("d",[JobStartDate]-1,[JobFinishDate])

[Killer42]

I think your problem is more basic than that. If you start a job on 17/11/06 and finish on 18/11/06, I assume you want to return 2 days but this will only return 1.

Good point! You could just add 1 to the result, in that case.

=DateDiff("d",[JobStartDate],[JobFinishDate])+1

Not much difference either way I suppose, Mary.

[antheana]

I think your problem is more basic than that. If you start a job on 17/11/06 and finish on 18/11/06, I assume you want to return 2 days but this will only return 1.

=DateDiff("d", #17/11/2006#, #18/11/2006#)

This should solve your problem:

=DateDiff("d",[JobStartDate]-1,[JobFinishDate])

Complete and utter genius!!!

Thank you all for your help - problem solved. I added -1 to JobStartDate and with the dates (jobstartdate) 17/11/2006 and (jobfinishdate) 17/11/06 the joblength came back as 1 not 0.

So thanks again. It is much appreciated :)

[MMcCarthy]

Good point! You could just add 1 to the result, in that case.

=DateDiff("d",[JobStartDate],[JobFinishDate])+1

Not much difference either way I suppose, Mary.

Either way it works, the problem is DateDiff() doesn't include the first date in it's count.

Mary

[Killer42]

Either way it works, the problem is DateDiff() doesn't include the first date in it's count.
Mary

Which is only as it should be, of course. I mean, you could hardly claim that the difference between a date and the same date is a day.

[MMcCarthy]

Which is only as it should be, of course. I mean, you could hardly claim that the difference between a date and the same date is a day.

True!

Mary