Excel Formula - Work Hours Computation

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,993
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
Staff No.Issued TimeIssued DateissdatetimeCheckout TimeCheckout DatecheckdatetimeHours Worked
567317:24:1112/12/201812 Dec 18 07:2417:41:4612/12/201812 Dec 18 17:4110.29305556
586407:12:3612/12/201812 Dec 18 07:1219:30:4612/12/201812 Dec 18 19:3012.30277778
567567:12:2412/12/201812 Dec 18 07:1219:47:2012/12/201812 Dec 18 19:4712.58222222
575417:11:2612/12/201812 Dec 18 07:1119:31:2312/12/201812 Dec 18 19:3112.3325
567357:08:3212/12/201812 Dec 18 07:0819:31:1012/12/201812 Dec 18 19:3112.37722222
567527:08:2512/12/201812 Dec 18 07:0819:47:2612/12/201812 Dec 18 19:4712.65027778
567567:07:5712/12/201812 Dec 18 07:0719:46:3712/12/201812 Dec 18 19:4612.64444444
586737:03:2512/12/201812 Dec 18 07:0319:30:5712/12/201812 Dec 18 19:3012.45888889
554437:00:5712/12/201812 Dec 18 07:0019:46:4212/12/201812 Dec 18 19:4612.7625
587936:53:3712/12/201812 Dec 18 06:5319:30:3212/12/201812 Dec 18 19:3012.61527778
566266:46:1912/12/201812 Dec 18 06:4619:46:4912/12/201812 Dec 18 19:4613.00833333
5766119:01:1412/11/201811 Dec 18 19:017:51:1512/12/201812 Dec 18 07:5112.83361111
5675518:58:5812/11/201811 Dec 18 18:587:31:0412/12/201812 Dec 18 07:3112.535
5717818:48:2512/11/201811 Dec 18 18:487:30:3912/12/201812 Dec 18 07:3012.70388889
5676018:46:1312/11/201811 Dec 18 18:467:51:1412/12/201812 Dec 18 07:5113.08361111
5572818:33:1712/11/201811 Dec 18 18:337:30:5512/12/201812 Dec 18 07:3012.96055556
567357:17:5412/11/201811 Dec 18 07:1719:31:2212/11/201811 Dec 18 19:3112.22444444
567317:17:1612/11/201811 Dec 18 07:1717:44:5012/11/201811 Dec 18 17:4410.45944444

<colgroup><col style="width: 61px"><col width="102"><col width="102"><col width="102"><col width="102"><col width="102"><col width="112"><col width="112"></colgroup><tbody>
</tbody>
 

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
80
Hi oldbrewer,

Thanks for helping me again. I think your computation isn't based on the shift schedule on the second sheet? Ideally, I would only want to count the number of hours within the shift schedule.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
Staff No.Issued TimeIssued DateissdatetimeCheckout TimeCheckout DatecheckdatetimeHours Worked
567317:24:1112/12/201812 Dec 18 07:2417:41:4612/12/201812 Dec 18 17:4110.29305556
586407:12:3612/12/201812 Dec 18 07:1219:30:4612/12/201812 Dec 18 19:3012.30277778
567567:12:2412/12/201812 Dec 18 07:1219:47:2012/12/201812 Dec 18 19:4712.58222222
575417:11:2612/12/201812 Dec 18 07:1119:31:2312/12/201812 Dec 18 19:3112.3325
567357:08:3212/12/201812 Dec 18 07:0819:31:1012/12/201812 Dec 18 19:3112.37722222
567527:08:2512/12/201812 Dec 18 07:0819:47:2612/12/201812 Dec 18 19:4712.65027778
567567:07:5712/12/201812 Dec 18 07:0719:46:3712/12/201812 Dec 18 19:4612.64444444
586737:03:2512/12/201812 Dec 18 07:0319:30:5712/12/201812 Dec 18 19:3012.45888889
554437:00:5712/12/201812 Dec 18 07:0019:46:4212/12/201812 Dec 18 19:4612.7625
587936:53:3712/12/201812 Dec 18 06:5319:30:3212/12/201812 Dec 18 19:3012.61527778
566266:46:1912/12/201812 Dec 18 06:4619:46:4912/12/201812 Dec 18 19:4613.00833333
5766119:01:1412/11/201811 Dec 18 19:017:51:1512/12/201812 Dec 18 07:5112.83361111
5675518:58:5812/11/201811 Dec 18 18:587:31:0412/12/201812 Dec 18 07:3112.535
5717818:48:2512/11/201811 Dec 18 18:487:30:3912/12/201812 Dec 18 07:3012.70388889
5676018:46:1312/11/201811 Dec 18 18:467:51:1412/12/201812 Dec 18 07:5113.08361111
5572818:33:1712/11/201811 Dec 18 18:337:30:5512/12/201812 Dec 18 07:3012.96055556
567357:17:5412/11/201811 Dec 18 07:1719:31:2212/11/201811 Dec 18 19:3112.22444444
567317:17:1612/11/201811 Dec 18 07:1717:44:5012/11/201811 Dec 18 17:4410.45944444

<tbody>
</tbody>
 
Last edited:

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,993
i didn't see it lol but 56731 was booked for 7am to 7pm yet clocked in late and clocked out early - and what if somebody starts 7 pm friday until 7 am saturday - does the sat sun allowance take precedence
 

Watch MrExcel Video

Forum statistics

Threads
1,096,290
Messages
5,449,491
Members
405,567
Latest member
thantzaw2005

This Week's Hot Topics

Top