WHAT IS THE FORMULA TO CALCULATE HOURS BETWEEN TWO TIMES

B

babu

i want to calculate with the help of excel worksheet the hour worked by an
employee if his in and out timings are given. for example if ones in time is
say 9.30am and his out time is 6.42pm what is the total hours worked by him.
to find out this what is the formula used?
 
M

Myrna Larson

Just subtract and format as time. If you need hours in order to calculate pay,
it's =(B1-A1)*24*C1 where start time is in A1, end time in B1, hourly rate in
C1.
 
G

Guest

Say A1 contains 9:30 AM, and A2 contains 6:42 PM.

You could say "=HOUR(A2)-HOUR(A1)", or
equivalentally "=HOUR(A2-A1)". These formulas would return
the value 9, for 9 hours -- minutes are ignored. You could
get minutes by saying "=MINUTE(A2-A1)". It would give you
12 minutes; and if the time weren't 6:42 PM but 6:28 PM,
it would still calculate the difference between 6:28 PM
and 9:30 AM as 58 minutes (not negative 2 minutes).
 
J

Jay

i want to calculate with the help of excel worksheet the hour worked
by an employee if his in and out timings are given. for example if
ones in time is say 9.30am and his out time is 6.42pm what is the
total hours worked by him. to find out this what is the formula used?
Click to expand...

To allow the shift to span midnight, I suggest keeping both date and time.

Here's an example:

Start time in cell A1: 9/27/04 9:30 AM

End time in cell B1: 9/27/04 6:42 PM

Total hours worked in cell C1: =24*(B1-A1)

Format C1 as a number to get the result in hours and a fraction: 9.20 in
the example.

Background: Excel stores a date/time in whole days and fraction of a day.
So multiplying the difference by 24 gives hours.
 
A

Arvi Laanemets

Hi


Jay said:
To allow the shift to span midnight, I suggest keeping both date and time.
Click to expand...


No need for this, so long the interval between start and end time is less
than 24 hours!

Here's an example:

Start time in cell A1: 9/27/04 9:30 AM

End time in cell B1: 9/27/04 6:42 PM

Total hours worked in cell C1: =24*(B1-A1)
Click to expand...

A1=9:30
B1=18:42
C1=B1-A1+(B1<A1)
formatted as Time, or
C1=24*(B1-A1+(B1<A1))
formatted as Number/General


Arvi Laanemets
 
Top