# Thread: Difference between dates - not as simple as I'd hoped!

1. Registered User
Join Date
May 2011
Posts
6

## Unanswered: Difference between dates - not as simple as I'd hoped!

Hi

I'm newly registerd on this forum but have lurked for a while and picked up many tips, so thanks for those! However cant find anything to help with this one:

I have two date fields:
[STARTDATE] and [ENDDATE] (both in format dd/mm/yyyy) providing the dates someone arrives (start) and the departure date (end).

Each person is only allowed to stay a permitted number of days each year:
1/April - 31/March.

I want to be able to work out the number of days between the two dates then take this from the total they are allowed (which I can do).

The problem lies when the dates span the two financial years ie starts before 31 March and ends after 1 April.

If they stay from 28/March - 05/April. I'd like the calculation to only include from the 01/April-05/April for that year and the 28/March-31/March to be included in the previous years total.

Im presuming it'll be some sort of IF statement? But not sure IF i'm correct!
Also... not sure where to start!

Hope this makes sense as I've rewritten it a dozen times!!

Thanks for any suggestions/help

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
there a date function call datediff()

mydays = datediff('d',[startdate],[enddate]) this will return the number of days between to dates

I have a written a function that work out what financial year we are in

Code:
```Function MMPYEAR(F)
If IsNull(F) Then Exit Function
f1 = year(F)
f2 = Month(F)
If f2 > 6 Then
MMPYEAR = (f1 + 0) & "/" & (f1 + 1)
Else
MMPYEAR = (f1 - 1) & "/" & (f1 + 0)
End If
End Function```
you need to change the 6 to your month

ThisYear = MMPYEAR([datevalue]) will return what f year we are in

fyear = MMPyear(today())

fyear = 2010/2011

I think you have to write Function to return the days

Code:
```function MyDates(StartDate,EndDate)

FY1 = MMPYEAR(Startdate)
FY2 = MMPYEAR(Endate)
vb_Days = 0
vb_EndDate = dateserial(year(startdate),3,31)
vb_StartDate = dateserial(year(startdate),4,1)

IF FY1<>FY2 then
' Differance Fyears so we do something here

vb_Days = datediff('d',startdate,vb_Enddate)

Else
' SAME Fyear

End if

End Function```
mine blank

this is off top of head untested do it on the way to work
Last edited by myle; 05-24-11 at 17:49. Reason: spelling

3. Registered User
Join Date
May 2011
Posts
6
Thanks for the help.

Its been over 8 years since I last seriously did any database work and im starting to piece things together bit by bit!

In a query where do I put the functions? I'm guessing that I wouldnt and I'd use it directly in a form then use that for the report?

Thanks
Last edited by Fordyboy; 05-24-11 at 18:14. Reason: typo

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
Put the Function in a Module

It could used in both

Then in the Query

Fyear:MMPYEAR([feildname])
Last edited by myle; 05-24-11 at 19:00. Reason: Spelling

5. Registered User
Join Date
May 2011
Posts
6
Thanks again for the help.

Got the year function now working which is great.

Still having difficulty with the other. Head is pounding as I've been working on this since about 3 this afternoon. Going to call it a night and have another look at it in the morning.

Thanks again

6. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,105
This is not as straight-forward as it appears on first glance - I spent a long time hammering out something similar (relating to accounting for housekeeping windows in downtime reports).
I've hammered out a quick truth table and worked out the following rules:
• If both dates are less than or equal to the end date of the financial year, the visit occurred in the "old" financial year.
• If the start date is less than or equal to the end date of the financial year and the end date is greater than the end date of the financial year, the visit is split between the two years
• If both dates are greater than the end date of the financial year, the visit occurred in the "new" financial year

Your query needs two calculated fields with between them will return at least one non-zero value showing the allocation for the visit between the financial years. The calculations themselves are rather trickier - I can look something up if you like?

7. Registered User
Join Date
May 2011
Posts
6
That would be great if you could.

8. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,105
On further reflection, my example might not be relevant. It's checking an incidence of downtime against two potential windows each day, and has an If statement with seven ElseIf clauses!

I've rearranged the truth table and illustrated nine scenarios, of which three are unlikely. I did this by considering three dates (01-Mar-2011, 31-Mar-2011 and 10-Apr-2011) in each combination. Three combinations result in a start date that is after the end date - these three can be ignored.

That left me with six scenarios. Three of them gave visiting periods entirely in the "old" financial year. However, assuming that your data integrity is maintained with respect to the end date being greater than the start date, all three of these can be identified by the criterion "[EndDate] <= FinYearEnd". Two of them gave visiting periods that are split across financial years. These are identified by the criteria "[StartDate] <= FinYearEnd AND [EndDate] > FinYearEnd". The final one gave a visiting period entirely in the "new" financial year, and is identified by the criterion "[StartDate] > FinYearEnd" (again relying on data integrity).

As to putting this into a query, it gets a bit trickier.
Working on the following basis:
The terms "old" and "new" are applied on the assumption that we are standing on the change point between financial years - in neither of them.
[Person] - person ID field
[StartDate] - start date field
[EndDate] - end date field
FinYearEnd() - function to return end date of financial year that contains a given date
Your query can hold the first three fields and then the calculated fields as follows.
Code:
```[FromOldAllowance] = IIf([StartDate] > FinYearEnd(), 0, IIf([EndDate] <= FinYearEnd(), DateDiff("d", [StartDate], [EndDate]) + 1, DateDiff("d", [StartDate], FinYearEnd()) + 1))
[FromNewAllowance] = IIf([EndDate] < FinYearEnd(), 0, IIf([StartDate] <= FinYearEnd(), DateDiff("d", FinYearEnd(), [EndDate]) + 1, DateDiff("d", [StartDate], [EndDate]) + 1))```
These will return between one and two non-zero values.

Apologies for the essay - I got carried away. Please note that this is untested, but it's based on calculations that seem to work.

9. Registered User
Join Date
May 2011
Posts
6
Didnt get a chance to try this today. Will have a look at it tomorrow.

10. Registered User
Join Date
May 2011
Posts
6
Spoken again to the end users of the database and discovered that this problem would only affect at max a dozen bookings each year.

I've devised a work around which they are happy to try.

However, I'm glad I asked the question as the coding I've acquired I've used in two other parts of the database so far to work out which financial year particular things have happened in.

Once I get everything else they are looking for working. I might pop back and take another deep look at the problem and get it working without the workaround.

11. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,105