Timesheet check in time tracker

M

muhleebbin

Active Member
Joined
Sep 30, 2017
Messages
252
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
I'm trying to automate the process of finding hourly employees discrepancy between time inputted in the timesheets vs. a clock in system that we have going.

The software spits out ugly data from our clock in system and was wondering if there's a way to simplify the data to give me the start times for each day and each employee. Below is an example of data

TimeTrax 11.23.20.xls
ABCDEFGHIJKLM
95TIME CARD REPORTEmployee Name:Test 1Page 11 of13
96Payroll Group: Shop / Employee Group: Shop - Default Emp Grp
97For The Pay Period: 11/23/2020 12:00 AM Thru 11/30/2020 12:00 AM
98Printed: 02-Dec-20 08:23 AM
99StatusActualEditedIn/OutRegOt1Ot2Total
100Tue Nov 24 07:00 AMTue Nov 24 07:00 AMIn
101RuleMissed Out
102Wed Nov 25 07:01 AMWed Nov 25 07:01 AMIn
103Wed Nov 25 12:01 PMWed Nov 25 12:01 PMOut5:005:00
104Wed Nov 25 12:31 PMWed Nov 25 12:31 PMIn
105RuleMissed Out
106Total5:000:000:005:00
107TIME CARD REPORTEmployee Name:Test 2Page 12 of13
108Payroll Group: Shop / Employee Group: Shop - Default Emp Grp
109For The Pay Period: 11/23/2020 12:00 AM Thru 11/30/2020 12:00 AM
110Printed: 02-Dec-20 08:23 AM
111StatusActualEditedIn/OutRegOt1Ot2Total
112Mon Nov 23 07:18 AMMon Nov 23 07:18 AMIn
113Mon Nov 23 12:01 PMMon Nov 23 12:01 PMOut4:43
114Mon Nov 23 12:31 PMMon Nov 23 12:31 PMIn
115Mon Nov 23 03:30 PMMon Nov 23 03:30 PMOut2:597:42
116Tue Nov 24 07:00 AMTue Nov 24 07:00 AMIn
117Tue Nov 24 12:00 PMTue Nov 24 12:00 PMOut5:00
118Tue Nov 24 12:30 PMTue Nov 24 12:30 PMIn
119Tue Nov 24 03:32 PMTue Nov 24 03:32 PMOut3:028:02
120Wed Nov 25 07:02 AMWed Nov 25 07:02 AMIn
121Wed Nov 25 12:00 PMWed Nov 25 12:00 PMOut4:58
122Wed Nov 25 12:30 PMWed Nov 25 12:30 PMIn
123Wed Nov 25 01:31 PMWed Nov 25 01:31 PMOut1:015:59
124Total21:430:000:0021:43
Sheet1


If this is possible, I was then hoping to be able to create a secondary code that would then use that information and find if there's anyone that has a discrepancy of 10 minutes or more then it would change the font to red so that we can accurately pay our hourly employees. In the sample below Test 2 employee's 11/23/20 start time should be 7:18 clock in time vs the 7:00 inputted time.

SUBMITTED TIME REPORT -- 11232020 - Mon - 11292020 - Sun.xlsm
ABDEFHIQRST
5639 - Test 2Subtotal Hours : 3839
5711/23/2020Test7:00 AM12:00 PM310 - Countertop - Fabrication5REG109 - Catering KitchenPending12/01/2020 9:08 pm UTC39.1
5811/23/2020Test12:30 PM3:30 PM310 - Countertop - Fabrication3REG109 - Catering KitchenPending12/01/2020 9:09 pm UTC39.1
5911/24/2020Test7:00 AM11:00 AM200 - Steel Vessel Fabrication4REG102 - Lobby Water Feature 1Pending12/01/2020 9:11 pm UTC39.1
6011/24/2020Test11:00 AM12:00 PM310 - Countertop - Fabrication1REG101 - CountertopPending12/01/2020 9:12 pm UTC39.1
6111/24/2020Test12:30 PM3:30 PM310 - Countertop - Fabrication3REG101 - CountertopPending12/01/2020 9:13 pm UTC39.1
6211/25/2020Test7:00 AM12:00 PM210 - Backsplash - Fabrication5REG109 - Catering KitchenPending12/01/2020 9:52 pm UTC39.1
6311/25/2020Test12:30 PM1:30 PM20 - Shop and Equipment Maintenance1REG1 - NO PHASE - SamplesPending12/01/2020 9:53 pm UTC39.1
6411/26/2020Test7:00 AM3:00 PM1450 - LMT - Holiday8REG1 - NO PHASE - SamplesPending12/01/2020 9:55 pm UTC39.1
6511/27/2020Test7:00 AM3:00 PM1400 - LMT - PTO8REG1 - NO PHASE - SamplesPending12/01/2020 9:55 pm UTC39.1
6638
670
Sheet1
Cell Formulas
RangeFormula
H66H66=SUM($H$57:$H$65)
H67H67=IF(H66>40,IF((H66-SUMIF($F$57:$F$65,"*PTO*",$H$57:$H$65)-SUMIF($F$57:$F$65,"*Holiday*",$H$57:$H$65)>40),H66-SUMIF($F$57:$F$65,"*PTO*",$H$57:$H$65)-SUMIF($F$57:$F$65,"*Holiday*",$H$57:$H$65)-40,0),0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E474,E476:E621Cell Valuecontains "1 - General Costs"textNO


Thank you in advance for your assistance
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
You must log in or register to reply here.

Similar threads

O
  • Solved
Travel Duration allowing for more than one day and different time zones
Replies
7
Views
142
myall_blues
myall_blues
S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
455
Sundance_Kid
S
J
  • Solved
Excel Formula to highlight most recent record
Replies
3
Views
397
etaf
etaf
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
231
Raj1979
R
A
  • Question Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
430
myall_blues
myall_blues

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top