By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,631 Members | 826 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,631 IT Pros & Developers. It's quick & easy.

how to calculate the difference b/w two dates

P: 8
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 GetNumberOfWorkDays(sStartDate, sEndDate)
Dim iWorkDays

iWorkDays = (sEndDate - sStartDate) / 365.25

GetNumberOfWorkDays = 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
Nov 2 '06 #1
Share this Question
Share on Google+
5 Replies


pks00
Expert 100+
P: 280
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 GetNumberOfWorkDays(sStartDate, sEndDate)
Dim iWorkDays

iWorkDays = (sEndDate - sStartDate) / 365.25

GetNumberOfWorkDays = 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?
Nov 2 '06 #2

P: 8
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
Nov 2 '06 #3

P: 21
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,basis)

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.
Nov 2 '06 #4

NeoPa
Expert Mod 15k+
P: 31,419
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 GetNumberOfWorkDays(sStartDate, sEndDate)
Dim iWorkDays

iWorkDays = (sEndDate - sStartDate) / 365.25

GetNumberOfWorkDays = 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
Expand|Select|Wrap|Line Numbers
  1. ?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 :-
Expand|Select|Wrap|Line Numbers
  1. Public Function GetNumberOfWorkDays( _
  2.             sStartDate As Date, _
  3.             sEndDate As Date) As Double
  4.         GetNumberOfWorkDays = (sEndDate - sStartDate) / 365.25
  5. End Function
Nov 2 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function GetNumberOfWorkDays(sStartDate, sEndDate) As Double
  3. Dim iWorkDays As Double
  4.  
  5.     iWorkDays = (sEndDate - sStartDate) / 365.25
  6.     iWorkDays = Format(iWorkDays, "00.00") ' format to 2 decimal numbers
  7.     GetNumberOfWorkDays = iWorkDays
  8.  
  9. End Function
  10.  
  11. ' to test the return type
  12. Sub tempCode()
  13. Dim iWkDays As Double
  14.  
  15.     iWkDays = GetNumberOfWorkDays(#11/5/2004#, #5/1/2006#)
  16.  
  17. End Sub
  18.  
  19.  
Nov 7 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.