Calculating time into cash value

[fat controller]

I have a combo box which looks up a set of values from a table, some of these I have got to store, others I merely want to work with and calculate from. These columns are:

DutyNumber
WeekendOrBHPayment
Sun-ThursNightsPayment
FriSatBHNightsPayment
OTRate
WRDRate
AHDifference
SKPayment
UnSocPayment

Now for most of these, I 'think' I have it planned where they will go or how they will be used, but the OTRate is flummoxing me a wee bit.

On the form, I have an OT(time) field - the idea being that the user will enter the hours and minutes of overtime they have done (short time format - is this correct?)

Then, I would like the form to calculate the cash value (has to be stored in a field) by multiplying the hours/mins worked by the OT Rate - - but I don't want to store the OTRate unless I really have no other option. Once the calculation has been done, if the Rate is then 'forgotten' then that is no issue, as the only time it would be needed is if the field is revisited to amend any errors (so I am thinking the calculation should be done as an After Update event on the OT(time) field?)

Is there a way to do this with VBA?

 

[fat controller]

Shame I can't edit the title of this thread as things are expanding... (don't they always!)

The query above remains, however I now also have another related question - at the end of this process, I need to be able to output the data in two different forms:

- PDF report showing all the info including totals (so total hours overtime all totalled up, and the related cash value against it) along with all the other payments and deductions to give a final gross total.

- Excel report showing all of the info, BUT with the time broken down into a decimal to show hours and minutes separately. Apparently, the company that our payroll goes through only accepts it in this format (doesn't make much sense to me either!), so to make both ends of the process meet I will have to try and work out a way of getting the data out in the right format.

Can anyone help give me a nudge in the right direction?

 

[Minty]

I think you are storing the data in the wrong method, you need to normalise it.
You really need to store the Duty Number then link that to a table containing the various duty types.
You would only need to store the duty type and time / minutes OT claimed in this table. The duty type would store the appropriate OT rate allowing you to calculate correctly what you need.
Store the OT in minutes only to avoid conversion problems later.

 

[fat controller]

I have already tried to normalise it a bit, but I am not against trying further if it will fit in with the other needs (and its these other needs that often dictate how I have to store data);

The duty types and their variables are stored in their own table, however by the nature of what we do, they are variable - however, I need to be able to show what the values were at the time the statement was prepared (so not the ones that would be in the table subsequently) - I understand that I could of course store the duty data historically so that it is referenced according to the date of the statement, but I am concerned that would present problems for us.

If I store the OT as minutes, is it relatively straightforward to use that to give a cash value?

For example, I know that 120 minutes at £8 an hour is £16 - - but what sort of formula would it be to work that out?

 

[essaytee]

...snip...

If I store the OT as minutes, is it relatively straightforward to use that to give a cash value?

For example, I know that 120 minutes at £8 an hour is £16 - - but what sort of formula would it be to work that out?

The formula: mins/60 * rate.

I hear you ask about what if the minutes are not evenly divisible by 60. You can address this in your data entry form. Storing of the data is one thing, doesn't necessarily mean the users have to enter it in that same form. They could enter as hours & mins of which you'd convert to minutes for storing purposes. Along with that you would probably have a business rule that states only 30 min blocks permissible for overtime recording (or whatever).

If the minutes are not evenly divisible by 60 then the MOD function is your friend.

There are other ways to deal with time duration.

Steve.

 

[fat controller]

The overtime can be entirely variable down to the minute - would that not produce a decimal to multiply by the rate in the instance of a number not evenly divisible?

86 mins would be

86/60*8 so 11.47 - looks about right to me on the basis that 90 mins is an hour and a half, so £12 under those circumstances?

So, if a user puts in 01:26 for 1 hour and 26 mins, how do I convert that to mins for storage, and how to I get it back out at the other end in two ways - back into the original format for a PDF report, and into 1 + 26 for the decimal output?

 

[essaytee]

The overtime can be entirely variable down to the minute - would that not produce a decimal to multiply by the rate in the instance of a number not evenly divisible?

86 mins would be

86/60*8 so 11.47 - looks about right to me on the basis that 90 mins is an hour and a half, so £12 under those circumstances?

So, if a user puts in 01:26 for 1 hour and 26 mins, how do I convert that to mins for storage, and how to I get it back out at the other end in two ways - back into the original format for a PDF report, and into 1 + 26 for the decimal output?

That's where the MOD function is your friend. This function determines the remainder left from two numbers. If the value is zero there is no remainer.

Check this link.

 

[fat controller]

Thanks - just about to head to bed, but will refer to that in the morning

 

[essaytee]

Thanks - just about to head to bed, but will refer to that in the morning

No problems, I'll be out of action for the next 10 days, heading off to Bali on holidays.

 

[fat controller]

No problems, I'll be out of action for the next 10 days, heading off to Bali on holidays.

By the time you read this, I hope you had a nice holiday

 

[fat controller]

Right, I am being really thick with this - I get that the mod value will only return the remainder, so does that mean that everything the user puts in is divided by 60 (to be multiplied at the other end), yet the only number that gets stored is the number left over? If so, how does it know how many times the original number were wholly divisible by 60

That being the case, does the input/output get done in an unbound text box, and how do I convert the hours and minutes figure into minutes only to begin with?

I am wondering if this can't be handled a little more easily - there are only two rates for the over time (weekend, and weekday) and whilst the values for these are occasionally variable, they are now contained in another table, so can be looked up to create the cash value.

In short, all I will need is the total hours and minutes at weekend OT rate, and the same for weekday, which then get multiplied by the rate to give a cash value.
Then for the second output, I will need to use that total to give a whole number of hours and a whole number of minutes...... EDIT and just to be even more of a pain in the rear, that wouldn't be a 'proper' decimal either - 1:30 would need to display 1 + 30, NOT 1.5 as my brain suggests is common sense.

God I wish I was better with this stuff.....

 

[Minty]

Take a deep breath

You need to return both values - the left side e.g. So iTotalMinutes say 210 minutes = 2.5 Hours
Where 2 is the hours. We'll call the Mod value iHours .
You then need to subtract the minutes value of ihours from your total minutes to give you the correct value of minutes on the right side of your display.
So iMinutesLeftOver = iTotalMinutes - (ihours *60)
In the example iMinutesleftover = 210 - (2*60)
So iMinutesLeftOver = 210 - 180 which is 30.
So you then just need to build a string which is sDisplayedTime = iHours & ":" & iMinutesLeftOver

Your calculation in the background is still simple iTotalMinutes * MinuteRate

This is all Air code but hopefully makes thing as clear as mud ?

 

[fat controller]

OK, you are slowly cracking through my thick exterior, thank you

Thing is, I don't even really need to display the result in that format to the user - as long as the Excel sheet that exits from it as a report shows it in that form, everyone is happy.

Is it fairly easy to get the hh:m converted to show total minutes for the calculation?

Also, I assume that I wouldn't need to store any of this anywhere would I? I simply have 8 hh:mm values that then give a cash value (lookup at time report is extracted), and is then converted into minutes to give the hours and minutes as you describe above?

That, coupled with your advice above (made me do a couple of rethinks to normalise stuff) has certainly slimmed the amount of data being held right down, and the whole thing is now looking achievable rather than just being a nightmare Thank you

I am out of the loop pretty much all day and most of the night tomorrow, but I think the best thing to do now is crack on until I have things in a state where I can have some data in there to play with, and then probably come back to you for more advice as and when I fall over again?

 

[fat controller]

Right, I am back at this now, and have some data to play with which helps me see what I am doing (if you know what I mean)

So, I have two fields on the form at the moment - one (unbound) that is populated by the relevant rate per hour in cash (populated by a DLookup in the On Current event), and another which has totalled up the overtime completed in the four week timeframe - hh:mm, short time format.

Now, having just set the record source value of another text box to show one field multiplied by the other, it is now abundantly clear why I need to use the Mod function.

So, question is - do I use it in the record source for the totalcash textbox? Or do I need a separate unbound textbox or two to populate with the mod values to then pass to the final totalcash box? And how would/should the code for that look?

 

[fat controller]

I did a bit of searching on this, but I am not sure that I am not leading myself up a blind alley? I found code for a function (below) to convert time to mins, but when I use it as part of the control source of an unbound textbox, I get #Name?

The code is:

Function TimeToMin(MyTime)
TimeToMin = CInt(CDbl(MyTime) * 24 * 60)
End Function

and I have set the control source for the textbox as

=TimeToMin([txtSatOTTotalHrs])

Am I barking up the wrong tree?

 

[Minty]

Is txtSatOTTotalHrs the name of the control or the field?

 

[fat controller]

Is txtSatOTTotalHrs the name of the control or the field?

Its the name of the control - unbound textbox which adds up the total.

Multiplying by 1440 then dividing by 60 seems to have worked to allow me to show the cash value for the number of hours (or part thereof) that have been worked. All I need to do now is have those minutes shown as a number of hours and a number of minutes separately and I am swinging.

 

[Minty]

Okay - I can't test as but try adding a Me. in front of txtSatOTTotalHrs, to refer to the control value.

 

[fat controller]

I take it that this is the control source for a separate textbox? Sorry, I am struggling to get my head round this one

 

[Minty]

You may not be able to refer to the calculated control value in another control, you may have to do it in code, either an after update or on current event... I'm at home so can't check very easily - sorry.

In vba try setting me.txtBoxToDisplayMins = TimeToMin([me.txtSatOTTotalHrs])