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 + 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
5 Replies

 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

 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 ?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 Public Function GetNumberOfWorkDays( _             sStartDate As Date, _             sEndDate As Date) As Double         GetNumberOfWorkDays = (sEndDate - sStartDate) / 365.25 End Function Nov 2 '06 #5

 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   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     Nov 7 '06 #6

### Post your reply

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