Time Sheet formula required

[Syedali]

Hi Friends,

Kindly help on the attached file.

 

[Luke M]

Formula in I6:
=IF(I5<I4,1,0)+I5-I4
Formula in I7:
=MAX(0,I6-TIME(9,30,0))

Copy to the right as needed.

 

[Syedali]

Formula in I6:
=IF(I5<I4,1,0)+I5-I4
Formula in I7:
=MAX(0,I6-TIME(9,30,0))

Copy to the right as needed.

Hi Luke,

Thanks for immediate response.
I having one query, like if i came at (IN TIME) 9:30:00 AM AND MY out time is 12:15:00 PM , Means i left the office before my working hours completed.
So i need "0" in Extra hours column.
Attached sample.

 

[Luke M]

You did not copy the formula in I6 correctly. What I wrote:
=IF(I5<I4,1,0)+I5-I4
What you put:
=+IF(I4<5,((24-I4)+I5),(I5-I4))

If you put my formula in, then the Extra time will calculate correctly.

 

[Syedali]

You did not copy the formula in I6 correctly. What I wrote:
=IF(I5<I4,1,0)+I5-I4
What you put:
=+IF(I4<5,((24-I4)+I5),(I5-I4))

If you put my formula in, then the Extra time will calculate correctly.

Thank you so much luke.
I am posting another question but with same workflow. Kindly do the needful.

Extra hours I worked 31:00:00 I have calculated from sheet21-feb to 20-mar-2015.
I need is per our am getting 100rs .
How can multiply with 31 hours .
Kindly help am not able to find answer

 

[Luke M]

We need to convert the hours, which are stored in XL as decimal numbers, into whole numbers. To do that, we use the conversion of 24 hrs = 1 day. So, formula in F2:
=E2*24*100
To give you an answer of 3100rs