Screenshot 2023-10-30 165559.png
as in the attachment.
my criteria need
1. if only punch in but no punch out. consider no work.
2. if punch in and punch out time similiar , consider 1st punch in.
3. OT only valid if OT time > 1 hour, else no count OT.
4. if work on Weekend, consider OT.
5. Break only valid for work more than 6 hour,else no break. example work half day no minus break.
6. the table was done using power query for the time total hours calculation.
Last edited by ednoklv01; 10-30-2023 at 05:05 AM.
Welcome to the Forum ednoklv01!
Please see yellow banner at the top of the page and attach a sample file, disguising or removing any private data (real names, addresses, phone numbers, account numbers, email addresses, or anything that can be used to identify a person).
Having your file allows us to see and experiment with your data, layout, formulas, code, and possibly attach a file with a completed solution. If you are looking for formulas to produce a desired result, it helps if you create a mock-up of what you want the result to look like.
2. I don't understand what this means. What does "similar" mean for a time? What is "1st punch in" when there is only one per day?
Col D is the date and days .
Col F - start time( clocked in time)
Col G- End Time( clocked out time)
Col H- Col G subtract Col F
Col I- Col H in Hour( Gross Work Hour)
Col J- Break time, minus one hour if Gross Work Hour > 6 hour, else return 0
Col K- Over Time, criteria: if OT daily, after minus 8.5 hour is < 1 hour, return 0 OT.
- if Work on Weekends, count as OT.( no need minus 8.5 hour )
-----------------------------------------------------------------------------
a. if clock in only, forgot clock out.. regards as no work, unless got approval by HOD.
- the raw data is from DingTalk, so can use Dingtalk amend attendance, with approval from HOD.
We cannot implement or test a solution without your file. To get the best response, attach a file as requested.
attached is the excel file. The table are massaged using Power Query.
Try in H2:
I2:
Please Login or Register to view this content.
J2:Please Login or Register to view this content.
K2:Please Login or Register to view this content.
Please Login or Register to view this content.
Quang PT
one more criteria in OT Col K, if OT time is less or equal than one hour, return 0 OT.. HOD said OT only applies after 1 hour.
how to come with logic for ( if clock in only, forgot clock out.. regards as no work, unless got approval by HOD.)
In K2
Formula:
Please Login or Register to view this content.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks. but the formula also deducted the larger than 1 hour OT.
try
Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks