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!