testing for a Sunday?

[chris davies]

Hello peeps,
I have been working on a hours checking application, in which the user can enter their worked hours for the week and the application calculates how many hours at normal rate , time and half rate or double time rate have been worked for the week. All has been going swimmingly, with some help from here of course

What I would like to achieve now however, is, testing for a Sunday. This is because all hours worked on a Sunday, is paid at double time, regardless of the previous hours already worked for that week.

Example if a person worked 60 hours in a given week, there would be 47.5 hours of normal rate, 7.5 hours of time and a half rate and the remaining 5 hours would be at double time rate. But, if a person worked 60 hours in a week and 10 of those hours are worked on a Sunday, this would mean 47.5 at normal rate, only 2.5 hours at time and half rate and the remaining 10 hours at double time.

Therefore, is there a way I can test for a Sunday and perhaps automatically set a flag for the overtime calculation? Or is it possible with DateDiff() function, and if so could someone please assist me with the syntax or at least point me in a direction where I could solve this?

All replies gratefully recieved. Thanks in advance to anyone taking the time to read this post.

 

[gemma-the-husky]

easy peasy

if weekday(mydate) = vbsunday

 

[chris davies]

easy peasy

if weekday(mydate) = vbsunday

Hi there, thanks for the code, but could you please explain it? what is vbSunday? Can this be used for any day of the week vbMonday etc? and is weekday an internal function?

Please excuse my ignorance, I would just like to understand the code rather than just paste it in willy nilly so to speak, if you know what I mean.

 

[chris davies]

I forgot to add that my application uses a calendar object to select the startDate. I use an onclick event to set and calculate the endDate for the week. Basically the endDate is the startDate plus 6 days, so it would be the sixth day that is the Sunday. Also, I am now thinking this may be more difficult that previously thought, as up until now, I have been calculating the hours based on a total hours query which returns the sum of the hours for that week. Here is my SQL for the query:

SELECT Sum(Hours.Hours) AS ['Total Hours Worked']
FROM Hours
WHERE (((Hours.Date)>=forms.frmCalendarWeekHours.cmbStartDate And (Hours.Date)<=forms.frmCalendarWeekHours.cmbEndDate));

My head is beginning to hurt.

 

[gemma-the-husky]

doesnt matter - as long as you have a date type variable

weekday is a function that returns the day of week

it actually returns a number (either 0 to 6 or 1 to 7 - i forget which) indicating the day of the week, but VBA actually supplies constants vbsunday, vbmonday etc etc so you dont have to worry

{theres loads of constants - same with colors vbred, vbblue etc etc}

look at help for weekday function

caclulating total hours/overttime etc is possibly not trivial but i am sure you are heading the right way

 

[chris davies]

After walking away from the problem for a day or two, I realised that I can get the total hours for Monday thru to Saturday using existing my sum query (instead of Monday thru to Sunday), and fetch back the Sunday seperately, then add Sunday on at the end. Silly me

Sometimes one can stare at a problem for too long.

 

[chris davies]

as I said above, I changed my logic and run Sunday as a separate query and added it during the final calculation:

   ' if less than 47.5 hours are worked, then no overtime
        If Me.txtResult <= 47.5 Then
            Me.txtExtra1.Value = 0
            Me.txtExtra2.Value = 0
        End If
        
        'limit time and half to 7.5 hours or less
        If Me.txtResult.Value > 47.5 Then
           Me.txtExtra1.Value = Me.txtResult.Value - 47.5
                If Me.txtExtra1.Value > 7.5 Then
                   Me.txtExtra1.Value = 7.5
                End If
        End If
    
       'Double time calculation, display if more than 55 hours are worked
       'run Dlookup to fetch Sunday's hours
       SundayHours = DLookup("[Sunday]", "qrySunday")
       
        'test if worked over 55 hours to get main D/T calculation
        If Me.txtResult.Value > 55 Then
        'calculate double time hours, if Sunday worked add those hours as well
        'Sunday hours may be zero, no need to worry about this
           Me.txtExtra2.Value = (Me.txtResult - 55) + SundayHours
            Else
            'otherwise test if got D/T by just working the Sunday
                Me.txtExtra2.Value = SundayHours
        End If
    End If