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
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

littlema1007's avatar
littlema1007
Copper Contributor
Nov 03, 2017

Thank you! The formula for NETWORKDAY.INT worked perfectly.

I am still having issues with the 2nd half of my question. The extra leaves are counting but just not highlighting the color on the calendar.

Any additional assistance would be appreciated.

Resources