Simple difference between hours function problem

[ryan4646]

Hi Folks,

I am back after long time. I missed you guys. Ok, here is my problem.

I am making a time sheet. Now, in Column E, it should show the difference between each hour when I subtract C-D or D-C. But it is either showing me time, or ######## or if i change the format to number, a decimal value.

For example, for number 1, from C3 to D3, it should be 7 hours worked in total, but it is showing me pathetic result.

Note: Most of the time the work starts at night in one day (for example Saturday), and as it finish at 3 or 4 am, that means it is finishing in next day (sunday.


Hope i made it clear. Thanks in advance.

Attaching file

[jason.b75]

That happens when the result of a time calculation is negative. Your working results are also wrong because you have your formula backwards.

Try this one in E3, then fill down

=D3-C3+(D3<C3)

Or, to explain the logic, =Finish time - Start time + (Finish time less than Start time) which adds a day to the finish time, which turns 03:00 into 27:00, so the time can be calculated correctly.

[ryan4646]

awesome. it worked. but i am having another problem is that, calculating the pay.

In pay section, when i am multiplying the E3 and G3, it should show me $35, sensing 5*7. but it is showing me different one. may be because i am multiplying with hours. how to fix that ??

attaching file again and thanks in advance.

[samba_ravi]

try below in H3
=E3*G3*24
and you can also use
=MOD(D3-C3,1) in E3

[ryan4646]

Your guys are awesome.

This problem is solved now.. owhooo.. adding rep to everyone.