Solved

# Access Date Range bleeding into another year.

Posted on 2015-01-23
Medium Priority
81 Views
In Access, I have a ArriveDate and LeaveDate, I need to count the dates in the current year.  When the ArriveDate is 12/29/15 and the LeaveDate is 1/3/16, I only want it to count the 3 days.  I am currently using DateDiff("d",[ArriveDate],[LeaveDate]).. but this doesn't work when the LeaveDate goes into the next year... Additionally, when it is the next year, I need to begin to count the days of the current year.. So if the year is 2016 and the ArriveDate is 12/29/15 and the LeaveDate is 1/3/16- it should count 2 days.
0
Question by:Shaft960
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 12
• 7

LVL 18

Expert Comment

ID: 40567459
IIf(Year([arrivedate])=Year([leavedate]),DateDiff("d",[ArriveDate],[LeaveDate]),DateDiff("d",DateValue(Year(Now()) & "/1/1"),[LeaveDate]))
0

Author Comment

ID: 40567501
Simon,

Thanks for the comment, I keep getting an error message of wrong number of arguments.
0

LVL 18

Expert Comment

ID: 40567510
Sorry, I amended it after you tested. Please try again.

I'm not sure what range of dates you're working with. If date ranges are totally inside previous years, what days count would you want?
0

Author Comment

ID: 40567528
Simon,

There is no error wrong number of arguments error, But one part of the equation is not working right.

If the year is 2015 and I put in an ArrivalDate of 12/29/14 and a LeaveDate of 1/3/15... it gives me the correct number of days as 2, but if it is still 2015, if I put in ArrivalDate of 12/29/15 and a LeaveDate of 1/3/16... it gives me 367 days... Where it should be the 29, 30 and 31st for 3 days.
0

LVL 18

Expert Comment

ID: 40567538
OK. All durations that cross year boundaries are counted from 1st January of the year of the leaving date (rather than the year of the current date):
`````` IIf(Year([arrivedate])=Year([leavedate]),DateDiff("d",[ArriveDate],[LeaveDate]),DateDiff("d",DateValue(Year([LeaveDate]) & "/1/1"),[LeaveDate]))
``````
0

Author Comment

ID: 40567542
Simon,

I think we almost have it, but it is still a bit incorrect.. I put in this

Arrive                    Leave       Total
12/27/2015      1/4/2016            3

It should show 5 since it is 2015.  If it was 2016 then it would be correct at 3.  It should have counted 12/27 thru 12/31 for 5 days..

By the way, you are very good.
0

Author Comment

ID: 40567547
Also, when I change the year to 2016, if there is data that has dates of 12/15/15 to 12/20/15 it should show 0 since it is 2016... it is showing 5 right now
0

Author Comment

ID: 40567549
Basically, we are tracking how many days in a calendar year that a person camps in a campsite.  The can only camp in the park 28 times a year.  So we enter the arrival date and leave dates and it calculates.. it was working fine except when the year changes and it bleeds over
0

LVL 18

Expert Comment

ID: 40567578
This now gives days in the CURRENT year, but doesn't deal well with date ranges totally outside the current year. It can be expanded to deal with those if necessary. It depends how you are using it in your application. If it's on a form where you are only accepting bookings that include some days in the current year it should be OK.

``````DateDiff("d",IIf(Year([arrivedate])<Year(Now()),DateSerial(Year(Now()),1,1),[arrivedate]),IIf(Year([leavedate])>Year(Now()),DateSerial(Year(Now()),12,31),[leavedate]))
``````
0

Author Comment

ID: 40567594
Simon,

To start, I really appreciate your help.  I hate to keep giving you more issues.... but

When I put in an arrival date of 12/26/15 and a leave date of 1/2/16 - it shows 5 days.... it should show 6 days from 12/26 to 12/31.

When I put in Arrival of 1/2/16 and a leave date of 1/5/16 it should show Zero (0) because it is 2015, but it shows -2...
0

LVL 18

Expert Comment

ID: 40567627
Sorry, slow trip to understanding...

You want to count including both the beginning and end dates where it is current year.

Are you using this in a calculated form field or a query? I ask because this might be easier to do as a VBA function.

This should now cater for inclusive date-range counts in current year and zero for date ranges wholly in future years.
``````iif(year(arrivedate)>year(now()),0,DateDiff("d",IIf(Year([arrivedate])<Year(Now()),DateSerial(Year(Now()),1,1),[arrivedate]),IIf(Year([leavedate])>Year(Now()),DateSerial(Year(Now()),12,31),[leavedate]))+1)
``````

..It won't cater for date ranges wholly in previous years - is that an issue?
0

Author Comment

ID: 40567654
Simon,

I am using a query.  And it was working correctly with this formula except for where it intersects a new year.

CampTotal: DateDiff("d",[Arrivedate],[Leavedate])

We are tracking how many days in a year (Jan 1 to Jan 31) that someone camps in a campground.

The above formula works perfectly until you reach for example â€“ Arrival Date of 12/29/15 and Leave Date of 1/3/16.

In this example above, if it was still 2015, it should show 3 days.  If it was 2016 it should show 2 days for Jan 1 and Jan 2.  The leaving date does not count towards the total.

Thank you,

Steven
0

LVL 18

Accepted Solution

Simon earned 2000 total points
ID: 40567671
Thanks for your patience Steven. My last attempt before UK bedtime...

``````iif(year(arrivedate)>year(now()),0,DateDiff("d",IIf(Year([arrivedate])<Year(Now()),DateSerial(Year(Now()),1,1),[arrivedate]),IIf(Year([leavedate])>Year(Now()),DateSerial(Year(Now()+1),1,1),[leavedate])))
``````

I haven't time to test it as I have to go to another PC (which has a noisy keyboard that annoys my wife), but I hope it works. I've rolled back from including both start and end dates but changed the counting for LeaveDates in the next year.
Fingers crossed...
0

Author Comment

ID: 40567685
Simon,

Thanks for the attempt.  It is now showing

Arrival              Leave              Total
12/29/2015      1/3/2016            -362

It was working better with some of your earlier stuff..  I haven't used Experts Exchange in a while... This is my first question in a while...

Go to bed and maybe we can figure it out tomorrow... Thank you from California, USA
0

Author Comment

ID: 40567758
Simon,

I attempted this on my own and it is working but I need to figure the calculations where I have the text... Also, the "Today" function wasn't working so I needed to use "Now"

CampTotal: IIf(Year([ArriveDate])=Year(Now()) And Year([LeaveDate])=Year(Now()),DateDiff("d",[ArriveDate],[LeaveDate]),IIf(Year([ArriveDate])<Year(Now()) And Year([LeaveDate])<Year(Now()),"Zero",IIf(Year([ArriveDate])<Year(Now()) And Year([LeaveDate])=Year(Now()),"Jan1toLeaveDate",IIf(Year([ArriveDate])=Year(Now()) And Year([LeaveDate])>Year(Now()),"ArriveDateto1231","DoNothing"))))
0

Author Comment

ID: 40567787
Simon,

I figured it out... here is the formula.... I would have never figured it out without your help.

CampTotal: IIf(Year([ArriveDate])=Year(Now()) And Year([LeaveDate])=Year(Now()),DateDiff("d",[ArriveDate],[LeaveDate]),IIf(Year([ArriveDate])<Year(Now()) And Year([LeaveDate])<Year(Now()),0,IIf(Year([ArriveDate])<Year(Now()) And Year([LeaveDate])=Year(Now()),DateDiff("d",DateSerial(Year([LeaveDate]),1,1),[LeaveDate]),IIf(Year([ArriveDate])=Year(Now()) And Year([LeaveDate])>Year(Now()),DateDiff("d",[ArriveDate],DateSerial(Year([ArriveDate]),12,31)+1),0))))
0

Author Closing Comment

ID: 40567790
I would have never been able to complete the formula without the help of Simon.  Thank you.
0

LVL 18

Expert Comment

ID: 40567949
Whew. I'm sorry that was such a struggle. I really should have gone to bed two hours earlier!
0

Author Comment

ID: 40567953
I'm still up... 0154 am in California
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no oâ€¦
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games. Â  The game allows for a choice of who goes first and keeps track of the number of wins forâ€¦
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process fromâ€¦
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This lâ€¦
###### Suggested Courses
Course of the Month15 days, 2 hours left to enroll