Calculate time in Excel 2010

I'm working on a kind of timesheet for my company, the problem is we have three shifts and so three different starting/ending hours.
First shift starts at 7:00 AM, second at 3:30 PM and third at 12:00 AM.

The timesheet I would like to create is that you just write down when someone started to work and when he ended his work, then Excel should automatically calculate how many hours someone worked, if someone worked overtime (worked over 8 hours a day) and if someone worked at night (from 10:00 PM to 6:00 AM). If someoneworked during night Excel should write down how many hours. And if someone worked overtime AND during the night Excel should write down how many hours were worked overtime and how many hours during the night seperately.

I managed to write formulas to calculate how many hours someone worked and how many hours were worked overtime.
My problem is to calculate the work done at night and to write overtime and night work seperately.

Here's a link with a picture for easier understanding:

http://pokit.org/get/img/0636feded4b...bad90e833f.jpg

Hi,

Welcome to the Forum.

Can you attach a sample file in excel format instead of image with enough data to demonstrate your requirement. Make sure your desired results are shown, mock them up manually if necessary. Remember to desensitize the file by removing all confidential information before upload!

See the following URL for help on how to upload a file.
http://www.excelforum.com/members/da...ch-a-file.html

Thanks for the welcome. Here's the file with the important information.

Hi,

Try the following formulae:

In D10:
=IF(OR(D7="",D9=""),"",IF(D9>D7,D9-D7,1-D7+MOD(D9,1)))

In D11:
=IF(OR(D7="",D9=""),"",IF(AND(D7>=6/24,D7<=22/24,D9>=6/24,D9<=22/24,D7<D9),0,IF(D7>D9,1-MAX(D7,D14)+MIN(D9,D15),IF(D7=0,MIN(D9,D15)-D7,MAX(D15,D9)-MAX(D7,D14)))))

In D12:
=IF(OR(D7="",D9=""),"",ABS(IF(D9>D7,D9-D7,1-D7+MOD(D9,1))-8/24))

drag these across.

see the attached file.

That could do the trick, but there is one more problem. If a worker hasn't worked a day or so, I can't write that he worked from 0:00 to 0:00 because Excel calculates 16 hours of overtime nor can I leave the hours unwritten because then the cell "Total hours" would say "#VALUE!"

I've already covered that in the formula that I posted in post #4. If a worker is absent on any day, you can leave the cells blank, the result won't be affected.

Please go through the excel file that I had uploaded and let me know if you face any further issues.

Originally Posted by cbatrody

I've already covered that in the formula that I posted in post #4. If a worker is absent on any day, you can leave the cells blank, the result won't be affected.

Please go through the excel file that I had uploaded and let me know if you face any further issues.

Okay will do by Friday and will let you know. Thanks for helping me out!

Hi cbatrody, your work is great! Is there just a way to include the Pause? The "Total daily working hours" minus the cell "Pause" gives me the "###" and "#VALUE!" in the cell "Total hours" , "Night shift (hours)" and "Overtime (hours)" when the other cells are left empty (in case someone doesn't come to work).

Hi longchamp,

See the attached file.

I've modified the formula in D10 as following:

=IF(OR(D7="",D9=""),"",IF(D9>D7,D9-D7,1-D7+MOD(D9,1))-D8)


In D12:
=IF(OR(D7="",D9=""),"",ABS(IF(D9>D7,D9-D7,1-D7+MOD(D9,1))-8/24)-D8)

That did the job, thank you very much!