Formula calculates working hrs.

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,107
Code:
=IF(COUNT(E3:F3) = 2, MOD(F3-E3,1)-"8:00", "")
Hello,
The formula given above calculating the extra hrs after 8 hrs.
Is there any way to change it if there are entries below 8hrs the formula will give me the negative result?
Sample

14:00 => 01:00 = 3
14:00 => 21:00 = -1

Many Thanks
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,615
Hello,

after quite a bit of changing, but formula not very elegant

=IF(COUNT(E3:F3)=2,IF(MOD(F3-E3,1)-"8:00"<0,TEXT((((F3-E3)*24)-8),"#"),MOD(F3-E3,1)-"8:00"),"")
 

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,107
Hello,
Thanks for the reply but I've got below result:)

8:00 PM3:00 AM-25

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,095,430
Messages
5,444,402
Members
405,280
Latest member
Galliganr0

This Week's Hot Topics

Top