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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

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,089,945
Messages
5,411,439
Members
403,370
Latest member
Massi_921

This Week's Hot Topics

Top