Forum Discussion
Solved
Excel Attendance Tracker Spreadsheet Formula Help
Hi all,
I downloaded a Excel Attendance template that Excel created.
Do you know how I can adjust this formula so it can calculate the weekends as well? I believe this formula only counts weekdays and non holidays.
For example, No Call No Show should count 4 not 2.
Here's the formula:
samza18 I'm surprised that this particular template is still being used. It's full of 'features' and is notoriously difficult to adjust/expand. To address your specific issue, this template assumes that employees are NEVER scheduled to work Saturdays and Sundays, nor will they EVER be scheduled to work on any of the listed public holidays. Any type of absence on any of these days is therefore excluded from the absence day count.
On the tab "Employee Leave Tracker" the number of working days covered by a period of absence is calculated in column F with the NETWORKDAYS function. That is the number picked-up by the counters on row 20 in the Calendar View. If you have no need for this sort of date intelligence and just want to count the number of days during a period of absence, change the formula in F4 to:
=[@[End date]]-[@[Start date]]+1It should copy itself down the entire table column.
Or perhaps use a hybrid form where you add an extra column (call it Days) that just counts days with the above formula and keep the original one but rename it to (for example) "W_Days" first. Then adjust the first argument in the SUMIFS formulas on row 20 to sum either "Days" or "W_Days".
samza18 I'm surprised that this particular template is still being used. It's full of 'features' and is notoriously difficult to adjust/expand. To address your specific issue, this template assumes that employees are NEVER scheduled to work Saturdays and Sundays, nor will they EVER be scheduled to work on any of the listed public holidays. Any type of absence on any of these days is therefore excluded from the absence day count.
On the tab "Employee Leave Tracker" the number of working days covered by a period of absence is calculated in column F with the NETWORKDAYS function. That is the number picked-up by the counters on row 20 in the Calendar View. If you have no need for this sort of date intelligence and just want to count the number of days during a period of absence, change the formula in F4 to:
=[@[End date]]-[@[Start date]]+1It should copy itself down the entire table column.
Or perhaps use a hybrid form where you add an extra column (call it Days) that just counts days with the above formula and keep the original one but rename it to (for example) "W_Days" first. Then adjust the first argument in the SUMIFS formulas on row 20 to sum either "Days" or "W_Days".
samza18 Glad you could get it to work. With regard to other / better templates, no I don't really know any. Sorry.
