Converting minutes into increments

[Guest]

I am a truck driver. There are no set start times nor finish times . we
start when the load is ready and punch out when delivery is complete. we are
paid in increments of 6 minutes. For example if I start at 8:00am and punch
out at 4:06pm payroll records the time as 8.1 hours. I have an employee time
sheet from the Excel 2000 Formulas book thats excellent its on page 168. I
need a frmula that converts minutes into increments of 6. its always rounded
down to nearest increment.

 

[Sandy Mann]

With the start time in A1 & finish time in A2 try:

=FLOOR((A2-A1),6/60/24)*24

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

Hi, Sandy

simpler, perhaps to use

=FLOOR((A2-A1)*24,0.1)

although if you also want to accommodate shifts which start one day and end
the next, e.g. 18:00 to 06:00 then use

=FLOOR(MOD(A2-A1,1)*24,0.1)

 

[Sandy Mann]

Hi Daddylonglegs,

Hi, Sandy

simpler, perhaps to use

=FLOOR((A2-A1)*24,0.1)

Yes I agree that it is simpler but, (and this is in no way meant as a
criticism because I respect your formula and code writing abilities), as has
been pointed out to me by a couple of people in these NG's, leaving the
calculation in gives clarity for future sheet maintenance. Additionally, in
these NG's where the OP may not be very well up on writing formulas, it may
be less obvious why you should round down to the nearest 0.1 rather than
6/60 which of course is the same thing but takes 1/4 nano-second longer.

In this I have a certain amount of sympathy. At work the person who looks
after our hydrotherapy pool is somewhat mathematically challenged. To help
him do the calculations for water balance etc., I gave him a very old Sharp
pocket computer, (early 1980's vintage), which had, (would you believe), a
1K memory. (Yes that's right a 1,024 byte memory!) I therefore had to cut
out all surplus key strokes when programming it and so almost all
calculations were worked out and only constants used. I was making
alterations to it recently and I found it very difficult because it would
have lines like:

A=INT((2.5-B)*459+0.5)

Why the 459? It was the result of a calculation involving the volume of the
pool, the % strength of the disinfecting agent, but when I came back to it
after a few years I was baffled until I worked it out again.

However, having said that that was my intention I still goofed. The
construction of your formula is much better and I should have written it as
=FLOOR((A2-A1)*24,6/60). My only excuse is that it was 10 to 1 on Sunday
morning and well past my bed time.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

Thank you Sandy.
This is what I am looking for. Now my goal is to program this into my
TI-83 Plus calculator. The lady who does payroll computes totals with her
fingers. With the formula programed in the calculator , I need only to prompt
the start and finish time. I checked the calculator manual and did not notice
the floor function. The reason I've mentioned this is because it is not
excell. And your response to daddylonglegs about your expierence using
calculators could maybe help me in achieving the goal of getting a formula
into my 83 PLUS.

 

[Sandy Mann]

Hi DBane,

I am afraid that you have misinterpreted what I was saying. The Sharp
Pocket Computer was not a calculator but was a real computer, which had
Microsoft Basic programming language installed in it. Calculators have
probably changed a lot since I last bought one (a Texas Instruments TI-68),
but I have never seen a calculator that had an INT function in it.

Assuming that your 83 Plus does not have an INT function then you will have
to do the discarding of the decimals yourself. On my TI-68 you can enter
Degrees Minutes and Seconds which will equate to time Hours, Minutes and
seconds. When I subtract the Finish Time from the Start Time, this gives me
the difference in decimal hours. Ex: 15 Deg 09 Min 0 Sec -8 Deg 0 Min 0 Sec
= 7.15

To round down to the nearest 6 minutes down, which as Daddylonglegs pointed
out is 0.1 hours, we would multiply by 10, use the INT() function to remove
the remaining decimal places and then divide the answer by 10 again. As I
said above without the INT function you will have to remove the decimals
yourself by multiplying by 10 to get 71.5 then subtract the 0.05 and then
dividing the 71 by 10 to get 7.1, (of course you will most likely have the
wit to short-circuit all that by simply subtracting 0.05. All that remains
is to multiply the result by the wage rate.

Not knowing your calculator's capabilities I do not know how much of that is
programmable into you calculator



--
Best of luck

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

Thank you Sandy
As always what I needed. I use the Genuis as my office assistant, I renamed
him Sandy. This works excellent with the calculator in the normal mode, but
when I try to edit this way in a program I get an error message, when I go
to, the cursor is flashing on the minute tick. Alpha( A degree B min - C
degree D min) store to E.
Of coarse using the minute tick , this is where it is flashing. do you have
any idea what this foolish polish truckdriver is doing wrong.