By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

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

P: 21
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:

Expand|Select|Wrap|Line Numbers
  1. =DateDiff("d",[JobStartDate],[JobFinishDate])
But it still comes up with one. Any ideas?

I am using MS Access 2003.
Nov 20 '06 #1
Share this Question
Share on Google+
9 Replies


Expert 5K+
P: 8,435
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:

Expand|Select|Wrap|Line Numbers
  1. =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.
Nov 20 '06 #2

Expert 5K+
P: 8,435
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
Expand|Select|Wrap|Line Numbers
  1. =DateDiff("d",[JobStartDate],[JobFinishDate])
  2.   - (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.
Expand|Select|Wrap|Line Numbers
  1. =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.
Nov 20 '06 #3

Expert 5K+
P: 8,435
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. :)
Nov 20 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. =DateDiff("d", #17/11/2006#, #18/11/2006#)
This should solve your problem:

Expand|Select|Wrap|Line Numbers
  1. =DateDiff("d",[JobStartDate]-1,[JobFinishDate])
Nov 20 '06 #5

Expert 5K+
P: 8,435
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.
Expand|Select|Wrap|Line Numbers
  1. =DateDiff("d",[JobStartDate],[JobFinishDate])+1
Not much difference either way I suppose, Mary.
Nov 20 '06 #6

P: 21
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.

Expand|Select|Wrap|Line Numbers
  1. =DateDiff("d", #17/11/2006#, #18/11/2006#)
This should solve your problem:

Expand|Select|Wrap|Line Numbers
  1. =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 :)
Nov 20 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Good point! You could just add 1 to the result, in that case.
Expand|Select|Wrap|Line Numbers
  1. =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
Nov 20 '06 #8

Expert 5K+
P: 8,435
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.
Nov 20 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 20 '06 #10

Post your reply

Sign in to post your reply or Sign up for a free account.