• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Help on Days

Jay.pot04

New Member
kindly assist me do an absenteesim based on the file attached
 

Attachments

  • Excel Exercise - Absenteeism Template.xlsx
    353.9 KB · Views: 8
Is Absenteeism defined as

Days Attending/31
or
Days Attending /(31-Off-VL)
 
You can use Countifs to work out all the values

eg:
Total Days =COUNTA(B10:AF10)
Work Days =COUNTIFS(B10:AF10,">0")
VL Days =COUNTIFS(B10:AF10,"VL")
OFF Days =COUNTIFS(B10:AF10,"OFF")
ABSENT Days =COUNTIFS(B10:AF10,"ABSENT")

You can then sue these in what ever combination you need to get the absenteeism
 
hi Hui,

I need to have absenteesim % based on login on a given schedule per person. VL and Off should not affect their absenteeism.i need to have the daily,weekly and monthly report to show this in a table. i have attached a file with the login logout of each person. thank you in advance.
 

Attachments

  • Excel Exercise - Absenteeism Template.xlsx
    353.9 KB · Views: 3
@Jay.pot04
For example:
Alano, Lea: 07/01/2016 & 09/01/2016
How would You count her daily working times?
I did to 'January Raw Data'-sheet, would I call 'vision',
how do login/logout have recorded.
There are dates in row 3, every column is 24hours.
Maybe some login times have to read to happen previous day?

If someone has worked 8hrs, is daily absenteesim % 33 (8/24)?
If only 8hrs in that week, is weekly absenteesim % ~5 (8/(24*7))
If only 8hrs in that month, is monthly absenteesim % ~0 (8/(24*7*31))
 

Attachments

  • Excel Exercise - Absenteeism Template.xlsb
    373.9 KB · Views: 2
I aplogize for the confusion. i have attached another file. hope this will help. thank you
 

Attachments

  • attendance.xlsx
    14.3 KB · Views: 3
hi,

here is the sample I would like to have assitance based on the login/logout data on the prior file I sent. thanks in advance.

Date 1-Jan
Values
Name Designation Absenteeism % # Late Minutes Late # Absent # PTO PTO %
Del Mar, Charles Intraday 0.00% 0 0.00 0 0 0.00%
Del Rosario, Rizza Intraday 0.00% 0 0.00 0 0 0.00%
Lozano, Marlon Intraday 100.00% 0 0.00 1 0 0.00%
Manalo, Myk Intraday 0.00% 1 1.58 0 0 0.00%
Milo, Carollyn Intraday 0.00% 1 2.46 0 0 0.00%
Ramirez, Maria Intraday 100.00% 0 0.00 1 0 0.00%
Subida, Romano Intraday 0.00% 1 2.54 0 0 0.00%
Torres, Marielle Intraday 0.00% 0 0.00 0 0 0.00%
Uvero, Hanzel Intraday 100.00% 0 0.00 1 0 0.00%
Uyehara, Po Intraday 100.00% 0 0.00 1 0 0.00%
Grand Total 40.00% 3 6.58 4 0 0.00%
 
@Jay.pot04
You gave 'Data 1-Jan' 'answers'.
You gave us 4-Jan -data.
Like 'Subida, Romano' has worked 08:56hrs =>
Absenteeism % are
Daily ~63% (1- (8:56/24:00)
Weekly ~98% (1- (8:56/(24:00*7))
Monthly ~100% (1- (8:56/(24:00*31))
Seems that some rules are still missing?
 
hi vletm,

Charles is actually right.if you can assist me create a template.Considering that january 4 and january 1 schedules are the same based on the file.anyone who exceeded schedule based on logout time should not affect their abseteeism % for the day. PTO should also not affect daily/weekly/month absenteeism. i will resend the file to show you what more I need. thank you!!
 

Attachments

  • ABS Template.xlsx
    25.6 KB · Views: 4
@Jay.pot04
As I wrote .. Seems that some rules are still missing?
How Jay.pot04 want to calculate:
Absenteeism % # Late Minutes Late # Absent # PTO PTO % ?
I just gave one sample, no matter which day or month!
Jay.pot04's sample Absenteeism % were 0% or 100%
How do You calculate those values?
%-calculation needs at least two values. So far...
Daily: if working time 8hrs, whole day 24hrs => means 8/24,
~33% working and ~66% not working! Absenteeism % is ~66%.
Late Minute: needs at least two values, where to compare?
and so on...
 
@vletm,

i apologize to confuse you,absenteeiism % should be calculated on the number of minutes each person is late per day(login vs scheduled). if the person came in scheduled or earlier than scheduled,he/she will have 0% absenteesim for the day, same as if he/she is on off or VL. thank you in advance.
 
@Jay.pot04 - one step closer
Where is scheduled? work in/out!
So far, I've found login - logout ... maybe planned days length?
PTO (paid Time off)?
There are terms, which should know to give answer (or You'll get only questions).
form You last file>> Agent 4615039:
04/01/2016 13:00-17:11,17:12-17:26,17:27-22:00,22:27-22:28
what would be result of Your needs?
 
hi,

the work schedule in/out is in the first tab of the file I sent previously. id like to have a template somewhat similar to the file attached. thank you for asiisting.
 

Attachments

  • Excel Exercise - Absenteeism Template.xlsx
    353.9 KB · Views: 3
Back
Top