X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Datediff Years with Decimals    
post Apr 3 2008, 10:02 AM

Posts: 354
Joined: 16-March 05
From: B'ham, AL

I'm trying to calculate years of service down to the Hundreths level, i.e., 5.83 years. However, I'm only able to get a full integer. I've searched through the forums but haven't found an answer. I'm currently using:
ateDiff("yyyy",Class_date.ADJUSTED_SERVICE_DATE,Date()) + (Date() < DateSerial(Year(Date()), Month([ADJUSTED_SERVICE_DATE]), Day([ADJUSTED_SERVICE_DATE])))
Is there any way to obtain a decimal format in the year?
NOTE: Special Thanks to Strive4Peace2008 in her explanation of the "+ (Date() < DateSerial(....." portion of the code. It never would have occurred to me that datediff was only calculating the year values and not looking at the month of the year. I found it in thread # 1539864.
Go to the top of the page
post Apr 3 2008, 10:04 AM

UtterAccess VIP
Posts: 13,031
Joined: 2-March 04
From: Leicester, UK

do the datediff on days and then divide by 365.
This doesn't take into accout leap years but that would have very small effect when rounding to 2 decimal places
Go to the top of the page
post Apr 3 2008, 10:29 AM

Posts: 354
Joined: 16-March 05
From: B'ham, AL

I thought of that and wondered about the leap year impact. I'm not sure where to put the /365 in my code though. I've tried putting it right after the datediff as:
DateDiff("dd",Class_date.ADJUSTED_SERVICE_DATE,Date())/365) +(Date()......
and then tried it at the end of the string as:
.... Day([ADJUSTED_SERVICE_DATE])))/365)
and then tried it again as just:
(DateDiff("dd",Class_date.ADJUSTED_SERVICE_DATE,Date()) /365)
and in each case I had 0 records posted due to a type conversion failure. What am I missing?
Go to the top of the page
post Apr 3 2008, 10:34 AM

Posts: 354
Joined: 16-March 05
From: B'ham, AL

I got it. I had to go back and format the the target field as
Data Type: Number
Field Size: Double
Format: Standard
Decimal Places: 2
My only problem now will be figuring out how to ensure this field is formatted in this manner next time I run this report.
Go to the top of the page
post Apr 3 2008, 02:58 PM

UtterAccess Editor
Posts: 17,951
Joined: 4-December 03
From: Northern Virginia, USA

To really decrease the effect of leap years .. try using this ...
Instead of 365.
I have this set up as a constant in most of my databases ....
Public Const conDaysPerYear = 365.242199
Go to the top of the page
post Apr 8 2008, 12:11 AM

Posts: 282
Joined: 27-February 06
From: Long Beach CA

Actually, the SPECIFICITY of the calculation would dictate how you are doing your calculation. The problem that is faced here is that there is no such calculation for the DURATION of a year. In addition, a date calculation can include parts of years which are both leap year and non-leap years. Which calculation would you use, divide by 365, or divide by 366? In short, your calculation AS AN EXACT LENGTH OF DURATION, is impossible to determine without defining the year.
rankly, that type of duration is not really logical. Since there are about 365.25 days in a year, technically you can say that January 1 midnight to January 1 midnight, of any year, is exactly 365.25 (or actually 365.242199 days if you really want to get down to the tenths of a second). But the way that we measure the dates, with times, does not match up to the actual time of a year. Therefore, there is no definition that we can base our calculation for a year.
Personally, I would use "day" as the highest level of calculation that you can use without error. ("Week" can also be used by using modular or divisor of 7, or "fortnight" with modular or divisor of 14). Otherwise, if you want an approximately close (but never exact) calculation, you can use 365.242199 as stated above.
Go to the top of the page
post Jul 26 2017, 10:17 PM

Posts: 1
Joined: 26-July 17

Please get the month difference in decimal and then divided by 12 to get the year difference.

Public Function YearDateDiffInDecimal(Date1 As Date, Date2 As Date) As Double
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim strTemp As String

M = ((Year(Date2) - Year(Date1)) * 12) + (Month(Date2) - Month(Date1))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + D + 1
End If
strTemp = M & Format(D / Day(DateSerial(Year(Date2), Month(Date2) + 1, "01") - 1), ".0#")
YearDateDiffInDecimal = CDbl(strTemp) / 12
End Function
Go to the top of the page
post Jul 27 2017, 08:43 AM

UtterAccess Editor
Posts: 18,223
Joined: 29-March 05
From: Wisconsin


I see you're responding to a discussion from 2008.

If you'd like to help people with current questions, you can use the "New Posts" and the "Unanswered Topics" links in the upper right corner of the page.


(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    23rd March 2018 - 11:39 PM