Forum Discussion

Raghavendra Bhat's avatar
Raghavendra Bhat
Copper Contributor
Apr 29, 2017

Employee attendance tracker using excel

Hi, I need to make saturday as working day. The below template in excel 2016 has Saturday and Sunday and weekend. How can i change this ? https://templates.office.com/en-us/Employee-attendance-trac...
excel
littlema1007's avatar
littlema1007
Copper Contributor
Oct 26, 2017

I have 2 questions:

 

1. I'm using the excel 2013 employee attendance tracker on excel but I need to be able to count sat & sun as work days. We work 7 days a week.

 

2. I have added more leave types but the colors are not corresponding on the calendar nor counting them...I have copied the formula over but it still isn't working properly.

 

Can anyone assist with either of these?

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 26, 2017

Hi,

 

If all days are working when in formula for Work Days change NETWORKDAYS on NETWORKDAY.INTL and add third parameter as the string with 7 zeroes (all day on the week are working)

=NETWORKDAYS.INTL(DATE(Calendar_Year,1,1),EDATE(DATE(Calendar_Year,1,1),12)-1,"0000000")

For the leaves if you added more types you have OR condition (in Leave 1 OR in Leave 2 OR in Leave 3, ...).

Straightforward way to correct formula in conditional formatting is to take sum of COUNTIFS for each leave (instead of only one leave in template)

=(
COUNTIFS(lstEmpNames,valSelEmployee,lstSdates,"<="&C6,lstEDates,">="&C6,lstHTypes,'Leave Types'!$B$4)+
COUNTIFS(lstEmpNames,valSelEmployee,lstSdates,"<="&C6,lstEDates,">="&C6,lstHTypes,'Leave Types'!$B$8)+
COUNTIFS(lstEmpNames,valSelEmployee,lstSdates,"<="&C6,lstEDates,">="&C6,lstHTypes,'Leave Types'!$B$9)
)>0

Like attached

Resources