Calculate time on form with 4 shifts, lunch, and total on Access Form

[alexrubio]

I'm having a very hard time calculating time on an access form. I have the following fields:

Shift1Start
Shift1End

Shift2Start
Shift2End

Shift3Start
Shift3End

Shift4Start
Shift4End

Lunch

TotalHours

The times entered above are on textboxes using medium time format (HH:MM AM or HH:MM PM). They do not cross over to the next day, they are all within one work day.

My goal is to get the "TotalHours " field to display a total time of the shifts, minus the "Lunch" field. TotalTime needs to be displayed in HH:MM AM/PM format.

I have tried:

=([Shift1End]-[Shift1Start])*24

Just to test before proceeding to attempt including the rest of the shifts, but I get the TotalHours field to display the time in decimal value. I have tried all kinds of variations but cannot nail the correct sintax/combination. I'm placing the code in the Control Source of the TotalHours field...

If anyone can please give me a hand would this I would really appreciate it.

In advance, Thank You!

[jforbes]

Are you using Unbound Fields? Or are you Bound to fields with a Date/Time Datatype? Are you getting an Error? What is it that is not working for you?

This part confused me:

My goal is to get the "TotalHours " field to display a total time of the shifts, minus the "Lunch" field. TotalTime needs to be displayed in HH:MM AM/PM format.

I would think the total time would be a Number and not a Formated Date/Time.

I think what you have, formula wise, will work against bound Controls, at least if the day is the same for both the start and end.
You may want to consider using the DateDiff() function. DateDiff() works on Dates and can work on Strings:

?DateDiff("s", "8:00am", "3:30pm")/3600
>7.5

[alexrubio]

Are you using Unbound Fields? Or are you Bound to fields with a Date/Time Datatype? Are you getting an Error? What is it that is not working for you?

This part confused me: I would think the total time would be a Number and not a Formated Date/Time.

I think what you have, formula wise, will work against bound Controls, at least if the day is the same for both the start and end.
You may want to consider using the DateDiff() function. DateDiff() works on Dates and can work on Strings:

?DateDiff("s", "8:00am", "3:30pm")/3600
>7.5

Hi jforbes, thanks for your reply,

Yes, the start, end, and Lunch fields are bound, but the totalhours field will not, as it will just soppose to calculate the total, and I need it to show in HH:MM hours and minutes, so if the total hours worked totals to three hours and twenty minutes it should show as 02:20 or 2:20...

I tried DateDiff, but was not successful, how would I pluck my fields into your suggestion above?

Thanks again,

Alex

[jforbes]

Ahh, now I understand what you are wanting for the Time Format. I think you will get what you want for this by changing the Format property for your TotalHours to "Short Time". You can accomplish the same thing in the ControlSource of TotalHours, but I think it's slightly better to have the Value of the Control as a numerical value instead of the String. That's getting nit picky on my part and you should do it however works best for you.

I think this would work for you as the ControlSource for TotalHours to start with:

=(DateDiff("s",[Shift1Start],[Shift1End])/86400)

What it does is gives you the percentage of a day. A Full day of 24 hours would equal a 1. So, 6 hours is 1/4 of a day or (1/24)*6=.25.

To include the Lunch calculation, you'll want something like this:

=((DateDiff("s",[Shift1Start],[Shift1End])-([Lunch]*3600))/86400)

You'll also need to put in the other Shifts. =)

As well as you may need to put in a Me.Recalc on some of your AfterUpdate Events. Putting in a Formula like this is a bit different in Access vs Excel and Access won't always catch the times it needs to recalc.

Good luck

[alexrubio]

Ahh, now I understand what you are wanting for the Time Format. I think you will get what you want for this by changing the Format property for your TotalHours to "Short Time". You can accomplish the same thing in the ControlSource of TotalHours, but I think it's slightly better to have the Value of the Control as a numerical value instead of the String. That's getting nit picky on my part and you should do it however works best for you.

I think this would work for you as the ControlSource for TotalHours to start with:

=(DateDiff("s",[Shift1Start],[Shift1End])/86400)

What it does is gives you the percentage of a day. A Full day of 24 hours would equal a 1. So, 6 hours is 1/4 of a day or (1/24)*6=.25.

To include the Lunch calculation, you'll want something like this:

=((DateDiff("s",[Shift1Start],[Shift1End])-([Lunch]*3600))/86400)

You'll also need to put in the other Shifts. =)

As well as you may need to put in a Me.Recalc on some of your AfterUpdate Events. Putting in a Formula like this is a bit different in Access vs Excel and Access won't always catch the times it needs to recalc.

Good luck

When I set format to Short Time it shows "0:00" in TotalHours regardless of the start and end time, if I don't, it shows "-3600. If I factor in the lunch, it forces me to populate the lunch field, response is "0:00" with Short Time Format regardless of times and with no format it changes to, for example if times are start "01:00 AM" and end "02:00 AM" total displayed is "-75600".

[jforbes]

The number that is created for TotalHours, should be between Zero and One. I'm guessing there is a minus sign instead of a division sign, like this:

=(DateDiff("s",[Shift1Start],[Shift1End])[icode]-[/icode]86400)

instead of:

=(DateDiff("s",[Shift1Start],[Shift1End])[icode]/[/icode]86400)

Once the number is between Zero and One, I believe the Format will start working correctly.

Then, to deal with the Lunch needing to be populated, you can deal with it in multiple ways. You can Default it on a new record, even if it is Zero. Of you can deal with it by using a Nz() to give you a Zero incase it is Null. Sorta like:

=((DateDiff("s",[Shift1Start],[Shift1End])-(Nz([Lunch], 0)*3600))/86400)

[alexrubio]

The number that is created for TotalHours, should be between Zero and One. I'm guessing there is a minus sign instead of a division sign, like this:

=(DateDiff("s",[Shift1Start],[Shift1End])[icode]-[/icode]86400)

instead of:

=(DateDiff("s",[Shift1Start],[Shift1End])[icode]/[/icode]86400)

Once the number is between Zero and One, I believe the Format will start working correctly.

Then, to deal with the Lunch needing to be populated, you can deal with it in multiple ways. You can Default it on a new record, even if it is Zero. Of you can deal with it by using a Nz() to give you a Zero incase it is Null. Sorta like:

=((DateDiff("s",[Shift1Start],[Shift1End])-(Nz([Lunch], 0)*3600))/86400)

Hi jforbes,

I'm going crazy with this thing, lol... I had a few parenthesis missing, didn't realize... Now I got it working fine with just one shift. If I factor in the Lunch the results are erratic... I could not get it to work with the field bound, and if I unbind and format to short time on say Start 01:00 AM and End 02:15 AM with a "0:30" Lunch, the Total displays "1:13"... The lunch field cannot be unbound because the employee record would not reflect that lunch time (unless there is another way to save it)... The other dilemma is factoring in the other shifts, but I guess we'll cross that bridge when we get there, if I can get one shift with lunch working at least we have an idea and can tweaq from there...

Thanks again for your continued help on this, really appreciate it...!

[jforbes]

I can understand your frustration. Getting it all to work on the Form without resorting to VBA is a bit of a magic trick.

If it is getting to you, you can do all this through VBA. It would give you greater control and you could also debug it to see what it is doing.

[alexrubio]

I can understand your frustration. Getting it all to work on the Form without resorting to VBA is a bit of a magic trick.

If it is getting to you, you can do all this through VBA. It would give you greater control and you could also debug it to see what it is doing.

If you can help me with that it would be FANTASTIC!!!. I just thought this was the only way to get it to work "live", meaning you get the results as you type into the fields and not have to wait for the record to be saved to see it...

[jforbes]

This is pretty straight forward. I built this function from the code above with a couple differences:

Private Sub Form_Current()
    Call doPaint
End Sub

Private Sub Shift1Start_AfterUpdate()
    Call doPaint
End Sub

Private Sub doPaint()

    Dim lTime As Long
    
    ' Calculate Time in Minutes
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![Shift1Start], Me![Shift1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![Shift2Start], Me![Shift2End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![Shift3Start], Me![Shift3End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![Shift4Start], Me![Shift4End]), 0))
    lTime = lTime - ((Nz([Lunch], 0) * 60) * 4) ' Times four for four Employees... maybe have four seperate instances of Lunch?
    
    ' Format and Display
    Me.txtTotalHours.Value = Right("00" & Round(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)

End Sub

The first difference is I switched to minutes instead of seconds as it seemed to make more sense.
Second is the use of NZ() to deal with Nulls. I mentioned this, but actually included it here.
Third was moving away from the Format on the TextBox. You'll need to make sure the Format is removed as well as making sure there is no ControlSource for the TotalHours TextBox. Since we are in VBA, we have additional options. The sample above may look a bit weird at first, but it is pretty trustworthy.

After you get your own version of the function and have it getting info from all the right fields and updating the TotalHours TextBox, you'll need to call the doPaint() function whenever you know this value could change. This is the "Live" functionality that you were talking about.
To do this, include a call like this:

Private Sub Shift1Start_AfterUpdate()
    Call doPaint
End Sub

on the AfterUpdate event for all your TextBoxes as well as the OnCurrent Event of the Form.

Hope this gets you going.

[alexrubio]

This is pretty straight forward. I built this function from the code above with a couple differences:

Private Sub doPaint()

    Dim lTime As Long
    
    ' Calculate Time in Minutes
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![Shift1Start], Me![Shift1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![Shift2Start], Me![Shift2End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![Shift3Start], Me![Shift3End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![Shift4Start], Me![Shift4End]), 0))
    lTime = lTime - ((Nz([Lunch], 0) * 60) * 4) ' Times four for four Employees... maybe have four seperate instances of Lunch?
    
    ' Format and Display
    Me.txtTotalHours.Value = Right("00" & Round(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)

End Sub

The first difference is I switched to minutes instead of seconds as it seemed to make more sense.
Second is the use of NZ() to deal with Nulls. I mentioned this, but actually included it here.
Third was moving away from the Format on the TextBox. You'll need to make sure the Format is removed as well as making sure there is no ControlSource for the TotalHours TextBox. Since we are in VBA, we have additional options. The sample above may look a bit weird at first, but it is pretty trustworthy.

After you get your own version of the function and have it getting info from all the right fields and updating the TotalHours TextBox, you'll need to call the doPaint() function whenever you know this value could change. This is the "Live" functionality that you were talking about.
To do this, include a call like this:

Private Sub Shift1Start_AfterUpdate()
    Call doPaint
End Sub

on the AfterUpdate event for all your TextBoxes as well as the OnCurrent Event of the Form.

Hope this gets you going.

Thanks a MILLION jforbes, going to get to work on this right away...

One quick question, where do I insert the initial code into?, The form's On Load?

Thanks Again!

[jforbes]

The doPaint Sub (or whatever you want to name it) shouldn't be in any event, just add it to the end of the Form's code. Then call it from the other events. I updated the code in Post#9 to include some of the other events to help clarify the structure and how the sub is called.

[alexrubio]

The doPaint Sub (or whatever you want to name it) shouldn't be in any event, just add it to the end of the Form's code. Then call it from the other events. I updated the code in Post#9 to include some of the other events to help clarify the structure and how the sub is called.

Okie dokie, got it working like champ, EXCEPT Lunch, the field gets totally ignored, I had formatting in it to force the user to use "00:00" but once I remove it, the totals don't make sense, on start 1:00 AM to 2:15 AM with a lunch of "1" the total is "-3:45" if I put "30" total comes out to "19:45"...

[jforbes]

Sounds like it's working. 1.25 hours - 4 hours = -3.75 hours.

Check out line 19. That's where Lunch is taken in consideration. You'll have to decide how you want to address that you are entering in four different shifts and then only one entry for Lunch. I put in a multiplier of four so that the one Field would apply to all four shifts. So, either three more Lunch TextBoxes need to be added, it's fine the way it is and maybe put a note on the Form letting the user know what is going on, or lastly, you could inspect each Shift and subtract a Lunch only if there is time entered against the shift.

Here is another version of the code where Lunch is only subtracted when a Shift has time against it.

Private Sub doPaint()

    Dim lTime As Long
    Dim lTemp As Long
    Dim lLunch As Long ' Long Lunch, Cracks me up
    
    ' Calculate Time in Minutes
    lTime = 0
    lLunch = (Nz([Lunch], 0) * 60)
    
    ' First Shift
    lTemp = (Nz(DateDiff("n", Me![Shift1Start], Me![Shift1End]), 0))
    If lTemp <> 0 Then lTime = lTime + lTemp - lLunch
    
    ' Second Shift
    lTemp = (Nz(DateDiff("n", Me![Shift2Start], Me![Shift2End]), 0))
    If lTemp <> 0 Then lTime = lTime + lTemp - lLunch
    
    ' Third Shift
    lTemp = (Nz(DateDiff("n", Me![Shift3Start], Me![Shift3End]), 0))
    If lTemp <> 0 Then lTime = lTime + lTemp - lLunch
    
    ' Fourth Shift
    lTemp = (Nz(DateDiff("n", Me![Shift4Start], Me![Shift4End]), 0))
    If lTemp <> 0 Then lTime = lTime + lTemp - lLunch
        
    ' Format and Display
    Me.txtTotalHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)

End Sub

One last thing, I used Round() instead of Fix() in the formatting portion. That was a mistake. Fix() is equivalent to truncating the value, which is what should be used in this case.

[alexrubio]

Sounds like it's working. 1.25 hours - 4 hours = -3.75 hours.

Check out line 19. That's where Lunch is taken in consideration. You'll have to decide how you want to address that you are entering in four different shifts and then only one entry for Lunch. I put in a multiplier of four so that the one Field would apply to all four shifts. So, either three more Lunch TextBoxes need to be added, it's fine the way it is and maybe put a note on the Form letting the user know what is going on, or lastly, you could inspect each Shift and subtract a Lunch only if there is time entered against the shift.

Here is another version of the code where Lunch is only subtracted when a Shift has time against it.

Private Sub doPaint()

    Dim lTime As Long
    Dim lTemp As Long
    Dim lLunch As Long ' Long Lunch, Cracks me up
    
    ' Calculate Time in Minutes
    lTime = 0
    lLunch = (Nz([Lunch], 0) * 60)
    
    ' First Shift
    lTemp = (Nz(DateDiff("n", Me![Shift1Start], Me![Shift1End]), 0))
    If lTemp <> 0 Then lTime = lTime + lTemp - lLunch
    
    ' Second Shift
    lTemp = (Nz(DateDiff("n", Me![Shift2Start], Me![Shift2End]), 0))
    If lTemp <> 0 Then lTime = lTime + lTemp - lLunch
    
    ' Third Shift
    lTemp = (Nz(DateDiff("n", Me![Shift3Start], Me![Shift3End]), 0))
    If lTemp <> 0 Then lTime = lTime + lTemp - lLunch
    
    ' Fourth Shift
    lTemp = (Nz(DateDiff("n", Me![Shift4Start], Me![Shift4End]), 0))
    If lTemp <> 0 Then lTime = lTime + lTemp - lLunch
        
    ' Format and Display
    Me.txtTotalHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)

End Sub

One last thing, I used Round() instead of Fix() in the formatting portion. That was a mistake. Fix() is equivalent to truncating the value, which is what should be used in this case.

Hi jforbes,

Hope you had a pleasant weekend... Ok, getting back to work here, the lunch time doesn't have to come out of any particular shift, it should just be deducted from the total so that the totalhours field shows just the hours worked. So if the Lunch field is blank no time should be deducted, if no shifts are entered the total should be blank or zero...

Let me know what you think and if I should proceed to implement your code changes above...

Thanks Much!