Access Date Range bleeding into another year.

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

Simon,

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

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?

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.

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]))

Select allOpen in new window

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.

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

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

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]))

Select allOpen in new window

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...

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)

Select allOpen in new window

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

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

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

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"))))

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))))

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

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

I'm still up... 0154 am in California