how to calculate the difference b/w two dates

[infobescom]

Hi

I am wrking on a application where i need to calculate the difference between two dates ..

here is the formula i am using
........

Public Function GetNumberOfWork Days(sStartDate , sEndDate)
Dim iWorkDays

iWorkDays = (sEndDate - sStartDate) / 365.25

GetNumberOfWork Days = iWorkDays
End Function

...............
this gives me the difference in years i.e in integer format for example if the difference b/w two date is 2.5 years then i get it as 3 years i.e the duration is rounded off to 3years in place of 2.5 years ...now i need to display this time duration in float format ..where the exact duration is displayed as 2.5 years .....

so pls...could anyone gimme a solution for this ..

Thanks
infobescom

[pks00]

Hi

I am wrking on a application where i need to calculate the difference between two dates ..

here is the formula i am using
........

Public Function GetNumberOfWork Days(sStartDate , sEndDate)
Dim iWorkDays

iWorkDays = (sEndDate - sStartDate) / 365.25

GetNumberOfWork Days = iWorkDays
End Function

...............
this gives me the difference in years i.e in integer format for example if the difference b/w two date is 2.5 years then i get it as 3 years i.e the duration is rounded off to 3years in place of 2.5 years ...now i need to display this time duration in float format ..where the exact duration is displayed as 2.5 years .....

so pls...could anyone gimme a solution for this ..

Thanks
infobescom

Why dont u just do a DateDiff using days as the interval
then calculate number of years based on the day?

[infobescom]

Why dont u just do a DateDiff using days as the interval
then calculate number of years based on the day?

we even tried out with that but the problem is that the year variable we are not able to get the accurate value like 2.5yrs ..instead we get 3 yrs ...so any soulution for that

[VALIS]

we even tried out with that but the problem is that the year variable we are not able to get the accurate value like 2.5yrs ..instead we get 3 yrs ...so any soulution for that

Try the YEARFRAC function ( See Access Help )

YEARFRAC
Calculates the fraction of the year represented by the number of whole days between two dates.

If this function returns the #NAME? error value, you may need to install msowcf.dll.

Syntax

YEARFRAC(start_ date,end_date,b asis)

Start_date is a date that represents the first (or start) date.

End_date is a date that represents the last (or end) date.

Basis is a number from 0 through 4 that specifies the type of day count basis to use.

[NeoPa]

Hi

I am wrking on a application where i need to calculate the difference between two dates ..

here is the formula i am using
........

Public Function GetNumberOfWork Days(sStartDate , sEndDate)
Dim iWorkDays

iWorkDays = (sEndDate - sStartDate) / 365.25

GetNumberOfWork Days = iWorkDays
End Function

...............
this gives me the difference in years i.e in integer format for example if the difference b/w two date is 2.5 years then i get it as 3 years i.e the duration is rounded off to 3years in place of 2.5 years ...now i need to display this time duration in float format ..where the exact duration is displayed as 2.5 years .....

so pls...could anyone gimme a solution for this ..

Thanks
infobescom

Firstly, I recommend always using explicit declarations of type for variables or functions.
Secondly, I would expect that this function actually DOES return a non-integer result, but that you assign it to an Integer (or Long) variable before looking.
Try (from VBA window use Ctrl-G) executing

?GetNumberOfWorkDays(#1 June 2004#, #1 January 2006#)

and see what result you get.
While you're about it, try this as an explicitly defined version of your own code :-

Public Function GetNumberOfWorkDays( _
			sStartDate As Date, _
			sEndDate As Date) As Double
        GetNumberOfWorkDays = (sEndDate - sStartDate) / 365.25
End Function

[MMcCarthy]

Your problem here is one of variable declaration.

You need to declare the return type from the function as a double

You need to specify the datatype of iWorkDays as a Double. Leaving it blank defaults to a variant.

Try this:

 
Public Function GetNumberOfWorkDays(sStartDate, sEndDate) As Double
Dim iWorkDays As Double
 
	iWorkDays = (sEndDate - sStartDate) / 365.25
	iWorkDays = Format(iWorkDays, "00.00") ' format to 2 decimal numbers
	GetNumberOfWorkDays = iWorkDays
	
End Function
 
' to test the return type
Sub tempCode()
Dim iWkDays As Double
 
	iWkDays = GetNumberOfWorkDays(#11/5/2004#, #5/1/2006#)
	
End Sub