How to calculate total hours worked minus 1 hour unpaid lunchbreak?

[japok2014]

Hi guys. I am very new here. So I have attached a sample sheet of my work. I have the time in and time out and my problem is how to total the hours worked and automatically deducting 1 hour lunch break every shift. Please help me. Thanks!

My Dillema.xlsx

[mcmahobt]

See the attached workbook for my proposed solution. Please note that I altered the format of your time input data a little.

Hope this helps!

[japok2014]

See the attached workbook for my proposed solution. Please note that I altered the format of your time input data a little.

Hope this helps!

Hi Thanks for the help. Really appreciate it. How did you come up with that formula? Also what is that time inpute? Thanks a lot.

[mcmahobt]

I'm not really sure with what you mean by how I came up with the formula? If you follow the thought process, it should be pretty straight forward, but I can further explain if you really need me to. As to the time inputs, it is entered as standard military time, with a custom format of 0###. This is to allow the formula that sums the hours to calculate properly.

[rsaravan]

Hi,

Eager to know about below formula,

what it means 1:1 & 4:4

=(SUMIF(1:1,"Out",4:4)-SUMIF(1:1,"In",4:4)-(COUNT(A4:AD4)/2))/100

Can you please explain.

Regards,
Saravanan R

[mcmahobt]

1:1 and 4:4 are referencing the entirety of rows 1 and 4. It is used as criteria ranges for the SUMIFS functions.

[rsaravan]

Hi,

Thank you very much for your rapid feedback.

New information, I learned today. Thank you very much.

Thanks & Regards,
Saravanan R