Talk About Network

Google


Register and Login
Nick
Password
Register create new account Sign up is FREE and you can post replies, new topics, bookmark posts and more!
Recover lost password


Software > Access Queries > Format Days To ...
Latest [ Topics | Posts ] Archive Post A New Topic Post a Reply
<< Topic < Post Post 1 of 1 Topic 41193 of 43104
Post > Topic >>

Format Days To Hours

by =?Utf-8?B?amxv?= <jlo@[EMAIL PROTECTED] > Aug 6, 2008 at 05:31 AM

In the past, I have used a formula to calculate how many work days has 
occurred between date fields.  I track days excluding Holidays and
Weekends 
from code I received years back from this discussion group.  Everything
works 
wonderful.

I have another situation similiar but the user wants to track time as well

as the number of days excluding Holidays and Weekends.  Is there a way to 
format this formula
CalcWorkDays([DateTimeReceived],[DateTimeAcknowledged]) 
so I can get hh:mm:ss?  Currently I am get 1 day. Is there a way to format
1 
day to 15 minutes or should I use another public function? 

For instance:
DateTimeReceived                      DateTimeAcknowledged
4/4/08 10:00 am                        4/4/08  10:15 am

Here is the code:
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date        ' To increment the date to compare
'Compliments of Dave Hargis

    intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1  'Start with total
days
                                                        'Add one to
include 
First Day
    dtmToday = dtmStart                                 'Initiate compare
date
    Do Until dtmToday > dtmEnd
        If Weekday(dtmToday, vbMonday) > 5 Then         'It is Saturday or

Sunday
            intTotalDays = intTotalDays - 1             'Take one day away
        ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
                "[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
            intTotalDays = intTotalDays - 1             'Take one day away

for the Holiday
        End If
    dtmToday = DateAdd("d", 1, dtmToday)                'Add a day for
next 
compare
    Loop    'Until dtmToday > dtmEnd                    'All days have
been 
compared
    CalcWorkDays = intTotalDays                         'Return the value
        
End Function
 




 1 Posts in Topic:
Format Days To Hours
=?Utf-8?B?amxv?= <jlo@  2008-08-06 05:31:01 

Post A Reply:
  Go here to Signup

AddThis Feed Button


About - Advertising - Contact - Frequently Asked Questions - Privacy Policy - Terms of Use - Signup

Contact
tan12V112 Thu Nov 20 21:53:44 CST 2008.