Calculating hours

[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?

 

[Ashish Mathur]

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

 

[pub]

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?

i always use this formula

=A2-A1+(A1>A2)

 

[David Biddulph]

=MOD(A2-A1,1) and make sure the result is formatted as time.

 

[MartinW]

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

 

[David Biddulph]

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