UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Overtime Calculation    
 
   
mhagen
post Oct 11 2009, 03:13 PM
Post #1

UtterAccess Addict
Posts: 118
From: Cape Town, South Africa



Hi there,
I am normally very good with formulas, but I am stuck n this one and need some help please. I am struggling to work out overtime
I have two fields:
DIDateDepart
DITimeBack
Times are someting like this:
7:00 10:00
10:00 15:00
15:00 18:00
18:00 21:00
Now I need to work out Overtime before 8:00, Overtime after 17:00 and Normal time (Between 08:00 and 17:00)
This is what I have to work out overtime before 8:00
=IIf(#8:00:00 AM# Between [ditimedepart] And [ditimeback],DateDiff("n",[DITimeDepart],#8:00:00 AM#)/60,0)
This is what I have to work out overtime after 17:00
=IIf([ditimedepart]>#5:00:00 PM#,DateDiff("n",[ditimedepart],[DITimeback])/60,0)
I just cant get the normal time, there must be an easier way.
Could someone please help me.
Much appreciated
Marlie
Go to the top of the page
 
+
AlbertKallal
post Oct 11 2009, 05:35 PM
Post #2

UtterAccess VIP
Posts: 1,989
From: Edmonton, Alberta Canada



Try:

CODE
IIf(start >= #8:00:00 AM# And start <= #5:00:00 PM#, DateDiff("n", #8:00:00 AM#, timeEnd), 0)


So, with your vars, try:

CODE
IIf([ditimedepart] Between #8:00:00 AM#  And #5:00:00 PM#,DateDiff("n", #8:00:00 AM#, [DITimeBack])/60,))


If the between syntax don't work, then use the example with >= as I have...

Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
.
Edited by: AlbertKallal on Sun Oct 11 18:36:31 EDT 2009.
Go to the top of the page
 
+
mhagen
post Oct 11 2009, 11:53 PM
Post #3

UtterAccess Addict
Posts: 118
From: Cape Town, South Africa



It is giving me two hours for between 11:00 and 12:00 which is only one hour. I also see my After 5 formula is wrong.
It looks like I am going to need to make up a different formula.
Any suggestions?
Regards
Marlie
Go to the top of the page
 
+
AlbertKallal
post Oct 12 2009, 03:18 AM
Post #4

UtterAccess VIP
Posts: 1,989
From: Edmonton, Alberta Canada



Well, I think the fact that times can overlap, such as 7:00 am to 10:00 am…that means only 2 regular hours.
While this could be coded as a expression, giving this some thought, it does start to get messy.
I have difficulty writing complex expressions. (unlike a few of the long time regulars here, they seem to have special brains that enables them to write these complex things!). However, my rule is if somthing seems too hard, then it likey the wrong approach..
So, I am going to suggest you build a custom function that can be used on reports and in quieres. The function to return regular hours for a given start and end time would look like:
CODE
Public Function RegHours(vStart As Variant, vend As Variant) As Variant

   Dim s       As Date
   Dim e       As Date
  
   RegHours = 0
  
   If IsNull(vStart) Then Exit Function
   If IsNull(vend) Then Exit Function
  
   If vStart <= #5:00:00 PM# And vend >= #8:00:00 AM# Then
        
      If vStart < #8:00:00 AM# Then
         s = #8:00:00 AM#
      Else
         s = vStart
      End If
      
      If vend > #5:00:00 PM# Then
         e = #5:00:00 PM#
      Else
         e = vend
      End If
      
      RegHours = DateDiff("n", s, e)
  
   End If
    
End Function

I written the above to return a value of 0 if nulls are passed which will often happen with queries or if you using this function in a report.
Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
+
mhagen
post Oct 12 2009, 06:49 AM
Post #5

UtterAccess Addict
Posts: 118
From: Cape Town, South Africa



Thanks, I will definately give it a go.
egards
Marlie
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 2nd September 2014 - 04:07 AM