Calculating hours

  • Thread starter Thread starter Marty
  • Start date Start date
M

Marty

I am working on a spreadsheet that will calculate the hours worked by a staff
member.
A1 has the start time of 16:00
A2 has a finish time of 01:30 the next morning.

What is the formula please to work out the hours worked by subtracting A2
from A1?
 
Hi,

You can use this formula. The assumption herein is that the 2 times (start
and end time) are on consecutive days I.e. the work hours are separate by a
day. Please ensure that the cell is formatted as General

(VALUE("24:00")-VALUE(A5))*24+(VALUE(B5)-VALUE("00:00"))*24

A5 has start time and B5 has end time

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
I am working on a spreadsheet that will calculate the hours worked by
a staff member.
A1 has the start time of 16:00
A2 has a finish time of 01:30 the next morning.

What is the formula please to work out the hours worked by subtracting
A2 from A1?
Click to expand...

i always use this formula

=A2-A1+(A1>A2)
 
Hi Marty,

Provided they are real times and not text,
Put this in A3
=IF(A1>A2,1+A2-A1,A2-A1)
and format as custom [h]:mm

HTH
Martin
 
.... or =MOD(A2-A1,1)*24 (and format as general or number) if you want the
answer in decimal hours.
 
Back
Top