473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Calculate Number of Years, Months and Days between 2 dates

Hi,

I have a continuous form based on a query ( I will also be creating a
report based on the same query).

There are 2 fields:

Date Obtained and Date Of Expiry

I want a further 3 columns to the right of these 2 fields to show the
number of Years, Months and Days between the 2 given dates.

In the query I have the dates as the first 2 columns. In the third
column I have:

Column 3 Has - Years: DateDiff("yyyy",[DateObtained],[DateOfExpiry])
Column 4 Has - Months: DateDiff("m",[DateObtained],[DateOfExpiry])
Column 5 Has - Days:
DateDiff("d",DateAdd("m",[Months],[DateObtained]),[DateOfExpiry])

However my data is not coming out quite how I would like it, for
example

Date Obtained Date Of Expiry Years Months Days
14/06/05 14/06/06 1 12 0
30/06/05 03/08/05 1 2 -27

On the first example I just want it to show: 1 0 0
On the second I want it to show: 0 1 3

I hope this makes sense and hope someone can help me out here.

Please make any recommended solutions as clear as possible.

Thanks in advance to anyone who takes the time to help me out here.

Regards.

Nov 13 '05 #1
6 27913
Carl:

Below is a query that, from my limited testing at least, appears to give you
the information you are looking for. Your second example says the result
should be 0 1 3, however, my calculations return 0 1 4, given that July is
31 days, plus the first three days of August. I had to adjust the month
calculation for the obtained Day being less than or equal the Expired Day as
the DateDiff function just subtracts Months.

SELECT TestDate.DateObtained, TestDate.DateExpired,
DateDiff("yyyy",[DateObtained],[DateExpired]) AS Years,
IIf(Day([DateObtained])<=Day([DateExpired]),DateDiff("m",[DateObtained],[DateExpired])-[Years]*12,DateDiff("m",[DateObtained],[DateExpired])-[Years]*12-1)
AS Months,
DateDiff("d",DateAdd("m",[Months],DateAdd("yyyy",[Years],[DateObtained])),[DateExpired])
AS Days
FROM TestDate;

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
<ca**********@newcastle.gov.uk> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...
Hi,

I have a continuous form based on a query ( I will also be creating a
report based on the same query).

There are 2 fields:

Date Obtained and Date Of Expiry

I want a further 3 columns to the right of these 2 fields to show the
number of Years, Months and Days between the 2 given dates.

In the query I have the dates as the first 2 columns. In the third
column I have:

Column 3 Has - Years: DateDiff("yyyy",[DateObtained],[DateOfExpiry])
Column 4 Has - Months: DateDiff("m",[DateObtained],[DateOfExpiry])
Column 5 Has - Days:
DateDiff("d",DateAdd("m",[Months],[DateObtained]),[DateOfExpiry])

However my data is not coming out quite how I would like it, for
example

Date Obtained Date Of Expiry Years Months Days
14/06/05 14/06/06 1 12 0
30/06/05 03/08/05 1 2 -27

On the first example I just want it to show: 1 0 0
On the second I want it to show: 0 1 3

I hope this makes sense and hope someone can help me out here.

Please make any recommended solutions as clear as possible.

Thanks in advance to anyone who takes the time to help me out here.

Regards.
Nov 13 '05 #2
Hi David,

Thanks very much for your help. This just about does it however I have
noticed that if the dates are:

1/4/05 and 31/3/08 the answer it gives is: 3 -1 30 where I think
it should be 2 11 30

Everything else seems to be spot on. Could you help me get around this
please.

Thanks again for your help.

Nov 13 '05 #3
If the pure query approach gets too messy you can try pasting the
following code into a module and then use:

Years: MyDateDiff("yyyy",[DateObtained],[DateOfExpiry])
Months: MyDateDiff("m",[DateObtained],[DateOfExpiry])
Days: MyDateDiff("d",[DateObtained],[DateOfExpiry])

---8<------8<------8<------8<------8<------8<------8<---

Public Function MyDateDiff(Interval, date1, date2)
Dim years, months, days, d1, d2
If IsNull(date1) Or IsNull(date2) Then
MyDateDiff = Null
Exit Function
End If
' ensure that d1 precedes d2
If date1 < date2 Then
d1 = date1
d2 = date2
Else
d1 = date2
d2 = date1
End If
years = DateDiff("yyyy", d1, d2)
d1 = DateAdd("yyyy", years, d1)
months = DateDiff("m", d1, d2)
If months < 0 Then
years = years - 1
months = months + 12
d1 = DateAdd("yyyy", -1, d1)
End If
d1 = DateAdd("m", months, d1)
days = DateDiff("d", d1, d2)
Select Case Interval
Case "d"
MyDateDiff = days
Case "m"
MyDateDiff = months
Case Else
MyDateDiff = years
End Select
End Function

Nov 13 '05 #4
Carl:

I adjusted the Years calculation to adjust for the month of the origin date
being greater than the expire date. This is the same logic I applied to the
Months calculation. With the adjustment, I return 2 11 30 for your example.

SELECT TestDate.DateObtained, TestDate.DateExpired,
IIf(Month([DateObtained])<=Month([DateExpired]),DateDiff("yyyy",[DateObtained],[DateExpired]),DateDiff("yyyy",[DateObtained],[DateExpired])-1)
AS Years,
IIf(Day([DateObtained])<=Day([DateExpired]),DateDiff("m",[DateObtained],[DateExpired])-[Years]*12,DateDiff("m",[DateObtained],[DateExpired])-[Years]*12-1)
AS Months,
DateDiff("d",DateAdd("m",[Months],DateAdd("yyyy",[Years],[DateObtained])),[DateExpired])
AS Days
FROM TestDate;

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
<ca**********@newcastle.gov.uk> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...
Hi David,

Thanks very much for your help. This just about does it however I have
noticed that if the dates are:

1/4/05 and 31/3/08 the answer it gives is: 3 -1 30 where I think
it should be 2 11 30

Everything else seems to be spot on. Could you help me get around this
please.

Thanks again for your help.
Nov 13 '05 #5
Hi David,

Thanks again for your response. I managed to get the same answer as
you using the following:

SELECT tblIncident.DateObtained, tblIncident.DateOfExpiry,
DateDiff("yyyy",[DateObtained],[DateOfExpiry])-IIf(Format([DateObtained],"mmdd")>Format([DateOfExpiry],"mmdd"),1,0)
AS Years,
IIf(Day([DateObtained])<=Day([DateOfExpiry]),DateDiff("m",[DateObtained],[DateOfExpiry])-[Years]*12,DateDiff("m",[DateObtained],[DateOfExpiry])-[Years]*12-1)
AS Months,
DateDiff("d",DateAdd("m",[Months],DateAdd("yyyy",[Years],[DateObtained])),[DateOfExpiry])
AS Days
FROM tblIncident;

Thanks again for taking the time in helping me out.

Could you tell me how to list the output so that it does not show
#Error if I have no DateObtained or DateOfExpiry in the table.

You gave me an example of putting this into a module. Can it be done
in a module using the SQL statements we have come up with as your
module version didn't give an error when fields were empty.

Thanks again for any pointers.

Regards.

Nov 13 '05 #6
Hi,

It's OK about the #Error thing, I asked the question before I put any
thought into it ... Sorry.

Here's what I put, don't know if it's an efficient way of doing it but
it seemed to work.

Cheers and thanks again for your time.

SELECT tblIncident.DateObtained, tblIncident.DateOfExpiry,
IIf([DateObtained] Is Null Or [DateOfExpiry] Is
Null,"",DateDiff("yyyy",[DateObtained],[DateOfExpiry])-IIf(Format([DateObtained],"mmdd")>Format([DateOfExpiry],"mmdd"),1,0))
AS Years,
IIf([DateObtained] Is Null Or [DateOfExpiry] Is
Null,"",IIf(Day([DateObtained])<=Day([DateOfExpiry]),DateDiff("m",[DateObtained],[DateOfExpiry])-[Years]*12,DateDiff("m",[DateObtained],[DateOfExpiry])-[Years]*12-1))
AS Months,
IIf([DateObtained] Is Null Or [DateOfExpiry] Is
Null,"",DateDiff("d",DateAdd("m",[Months],DateAdd("yyyy",[Years],[DateObtained])),[DateOfExpiry]))
AS Days
FROM tblIncident;

Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: David Gray | last post by:
Greetings all, Quick newbie type question: I would like to be able to trap non-numerical data entered into a textbox via CTRL+C and/or Shift+Insert. I realise that this data can be...
28
by: Steve | last post by:
Hi all How would I find out the average date when given a bunch of dates? For example, I want to find the average length in time from the following dates:...
26
by: Frank | last post by:
For my website i would like to display the age of my son in years, months, days and hours. For now i manage to get a result for totals. Like the total number of days. This is the beginning: ...
7
by: Bambero | last post by:
Hello all Problem like in subject. There is no problem when I want to count days between two dates. Problem is when I want to count years becouse of leap years. For ex. between 2002-11-19...
6
by: charliewest | last post by:
Can someone pls point me to or recommend the easiest way to calculate someone´s age using the TimeSpan object, in .NET CF? Isn´t there a simple way to use the TimeSpan object to calculate the...
6
by: Tom | last post by:
Hello, please help me, I need two write a C++ program that can calculate the number of days between two dates.
6
by: rohayre | last post by:
Im a long time java developer and actually have never done anything with java scripting. I'd like to write a short simple script for calculating a date in the future based on today's date and a...
9
by: Pierre Quentel | last post by:
Hi all, I have searched in the standard distribution if there was a function to return the difference between 2 dates expressed like an age : number of years, of months and days. The difference...
15
by: student4lifer | last post by:
Hello, I have 2 time fields dynamically generated in format "m/d/y H:m". Could someone show me a good function to calculate the time interval difference in minutes? I played with strtotime() but...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.