dealing with time

[Rhydderch]

I have been fooling around with a spreadsheet that allows me to enter my work
schedule and it calculates my approximate paycheck.

The place I work at uses the 24 hour format which made life somewhat easier;
but I can not help to think that there has got to be an easier way to work
with time.

Here is what I have done so far:

=SUM((((((IF(AND(J78>12,K78<12),(K78+24),K78))-(INT(IF(AND(J78>12,K78<12),(K78+24),K78))))*100)/60)+(INT((IF(AND(J78>12,K78<12),(K78+24),K78)))))-((((J78-(INT(J78)))*100)/60)+(INT(J78))))

This looks complicated and to a point it is at first glance; but here is
what it is doing:

First please note that I am entering times into the sheet in this format
10.3 & 16.45 are 10:30 am and 4:45 pm respectively.

In the formula J78 is the start time and K78 is the quiting time.

The first thing I needed to do (since I work a lot of overnight shifts) was
compare the 2 cells to see if the quiting time was after midnight and if so
add 24 hours to that time so the subtraction would work. This is done by the
combination of 2 logics [ IF(AND(J78>12,K78<12),(K78+24),K78) ].

Next I had to deal with the minutes portion of the cell (anything after the
decimal point). This is done by seperating off the decimal by subtracting the
whole number then multipling the decimal by 100 [ J78-(INT(J78)))*100) ].
This gives me the number of minutes but for payroll calculations I needed
percent of an hour so this number is then divided by 60, followed by adding
the whole hours back on [ J78-(INT(J78)))*100)/60)+(INT(J78) ].

Finally once this is completed with both cells, they can be subtracted from
one another....

What I have here works but can anyone tell me an easier way?

 

[Fred Smith]

The answer to the last question is there is definitely an easier way. Just
enter your data as times. If you start at 10:30, enter 10:30. If you end at
4:45pm, you can enter 16:45 or 4:45 pm.

Now Excel will do the arithmetic for you. To determine the number of hours
you worked in a day, subtract start time from end time, as in:

=k78-j78

To handle the situation where you work past midnight, use the following:
=mod(k78-j78,1)

In Excel, times are a fraction of a day. Therefore, to convert to hours,
multiply by 24. If, for example, your hourly rate is in L78, use the
following to determine how much you earned that day:

=mod(k78-j78,1)*24*L78

Regards,
Fred

 

[T. Valko]

What I have here works but can anyone tell me an easier way?

Yes. Enter the times as real times.

A1 = start time = 10:30
B1 = end time = 16:45

Then the formula is as simple as:

=MOD(B1-A1,1)

Formatted as h:mm returns 6:15

If you want the result in decimal format:

=MOD(B1-A1,1)*24

Formatted as General or Number returns 6.25

 

[Rick Rothstein]

If you want to stay with your current method of time entry, then this
simpler (than yours) formula should do what you want...

=24*MOD(SUBSTITUTE(TEXT(K78,"0.00"),".",":")-SUBSTITUTE(TEXT(J78,"0.00"),".",":"),1)

 

[Rhydderch]

Thank you for the MUCH easier ways of doing things; but you have to admit
that I was atleast creative....lol!

 

[David Biddulph]

Apart from the fact that there are other ways of doing it, your formula
seemed to have numerous unnecessary parentheses, and also the SUM function
didn't seem to be doing anything.

Can't
=SUM((((((IF(AND(J78>12,K78<12),(K78+24),K78))-(INT(IF(AND(J78>12,K78<12),(K78+24),K78))))*100)/60)+(INT((IF(AND(J78>12,K78<12),(K78+24),K78)))))-((((J78-(INT(J78)))*100)/60)+(INT(J78))))
be replaced by
=((((IF(AND(J78>12,K78<12),K78+24,K78)-INT(IF(AND(J78>12,K78<12),K78+24,K78)))*100)/60)+INT(IF(AND(J78>12,K78<12),K78+24,K78)))-((((J78-INT(J78))*100)/60)+INT(J78))
?

I think that it could probably be simplified further [for example by
replacing J78-INT(J78) by MOD(J78,1) and similarly elsewhere], but that
would have been a start.

If you do then do the MOD substitutions it makes it apparent that more of
the parentheses can go, and it simplifies further to
=MOD(IF(AND(J78>12,K78<12),K78+24,K78),1)*100/60+INT(IF(AND(J78>12,K78<12),K78+24,K78))-MOD(J78,1)*100/60+INT(J78)The simpler it is it, the more comprehensible.--David BiddulphRhydderch wrote:> Thank you for the MUCH easier ways of doing things; but you have to> admit that I was atleast creative....lol!>> "Rhydderch" wrote:>>> I have been fooling around with a spreadsheet that allows me to>> enter my work schedule and it calculates my approximate paycheck.>>>> The place I work at uses the 24 hour format which made life somewhat>> easier; but I can not help to think that there has got to be an>> easier way to work with time.>>>> Here is what I have done so far:>>>>=SUM((((((IF(AND(J78>12,K78<12),(K78+24),K78))-(INT(IF(AND(J78>12,K78<12),(K78+24),K78))))*100)/60)+(INT((IF(AND(J78>12,K78<12),(K78+24),K78)))))-((((J78-(INT(J78)))*100)/60)+(INT(J78))))>>>> This looks complicated and to a point it is at first glance; but>> here is what it is doing:>>>> First please note that I am entering times into the sheet in this>> format>> 10.3 & 16.45 are 10:30 am and 4:45 pm respectively.>>>> In the formula J78 is the start time and K78 is the quiting time.>>>> The first thing I needed to do (since I work a lot of overnight>> shifts) was compare the 2 cells to see if the quiting time was after>> midnight and if so add 24 hours to that time so the subtraction>> would work. This is done by the combination of 2 logics [>> IF(AND(J78>12,K78<12),(K78+24),K78) ].>>>> Next I had to deal with the minutes portion of the cell (anything>> after the decimal point). This is done by seperating off the decimal>> by subtracting the whole number then multipling the decimal by 100 [>> J78-(INT(J78)))*100) ]. This gives me the number of minutes but for>> payroll calculations I needed percent of an hour so this number is>> then divided by 60, followed by adding the whole hours back on [>> J78-(INT(J78)))*100)/60)+(INT(J78) ].>>>> Finally once this is completed with both cells, they can be>> subtracted from one another....>>>> What I have here works but can anyone tell me an easier way?