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

  • Anthony Skworch's avatar
    Anthony Skworch
    Copper Contributor
    Aug 01, 2018

    Sergei, who would I make the latest version of the attendance tracker a 7 day work week?

  • 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