Apr 29 2017 08:22 AM
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-tracker-TM02780235
Apr 29 2017 09:41 AM
Hi,
Calculating working days of the year template uses NETWORKDAYS function in the formula. You shall change it on NETWORKDAYS.INTL with proper parameters https://support.office.com/en-us/article/NETWORKDAYS-INTL-function-a9b26239-4f20-46a1-9ab8-4e925bfd5....
In addition, template grays Sat and Sun using Conditional formatting, within the rule formula you'll find
LEFT(C$5,1)="S"
Just change it on
C$5="SUN"
Use it selecting all days in the table
Apr 29 2017 09:45 AM - edited Apr 29 2017 09:58 AM
Dear,
Go to the Conditional Formatting Rules Manager, and select the last rule.
If you want the weedend to be Friday & Saturday, replace that formula by this one:
=OR(LEFT(C$5,2)="FR",LEFT(C$5,2)="SA", COUNTIF(lstHolidays, C6)>0)
Or, if you want it to be Friday only or Saturday, use this:
For Friday:
=OR(LEFT(C$5,2)="FR", COUNTIF(lstHolidays, C6)>0)
For Saturday:
=OR(LEFT(C$5,2)="SA", COUNTIF(lstHolidays, C6)>0)
Hope you find this information useful!
Regards,
May 08 2017 10:01 AM - edited May 08 2017 12:02 PM
Hi,
I run a department of around 40 colleagues, many of which have different days off. I would like to use this template but im not sure how i can implement it for each colleagues days off.
Any help would be greatly appreciated
Dan
May 08 2017 10:19 AM
Oct 26 2017 07:11 AM
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?
Oct 26 2017 08:45 AM
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
Nov 03 2017 10:17 AM
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.
Nov 03 2017 10:44 AM
Please check attached. All leaves are colored as one leave.
Mar 08 2018 02:20 PM - edited Mar 08 2018 02:24 PM
In my country Saturday and Sunday are counted in your "vacation" time frame. I see that you show how to select different dates for weekends, but I need the chart to count all days to be working days. So that the vacation count on the "Calendar View" and "Employee Leave Tracker" Show the correct amount of dates.
For example I have logged in the "Employee Leave Tracker" Employee 1 Start date Jan 29, 2018, End Date Feb 23rd, 2018; it counts as 20 days when I want it to count as 26 days (Including Sat and Sun).
Appreciate your help,
Thanks in advance.
Aug 01 2018 01:51 AM
Sergei, who would I make the latest version of the attendance tracker a 7 day work week?
Oct 12 2018 12:48 AM
Hi Sergei.
How if I want to categorize who are employees that get Sat & Sun Holiday and who are employees that only get Sun for Holiday using conditional formatting?
Kindly need your help ASAP.
Jan 19 2023 11:57 AM
Jun 04 2023 01:03 PM
Do we have a work around for this one ? I am also looking for custom options to pick /assign working days for employees / week off for specific employees
Jun 04 2023 02:05 PM
Jun 04 2023 02:37 PM
@peiyezhuThanks for your quick reply. For example, Employee1 works all 5 days (Mon-Fri), Employee2 works Saturday and Sunday and will avail week off on other two week days. I would like to have an option to customize workdays for employees accordingly .