SOLVED

# Excel Attendance Tracker Spreadsheet Formula Help

Copper Contributor

# Excel Attendance Tracker Spreadsheet Formula Help

Hi all,

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:

3 Replies
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Excel Attendance Tracker Spreadsheet Formula Help

@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]]+1``

It 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".

# Re: Excel Attendance Tracker Spreadsheet Formula Help

Works! Thank you! Would you happen to know better templates out of curiosity?

# Re: Excel Attendance Tracker Spreadsheet Formula Help

@samza18 Glad you could get it to work. With regard to other / better templates, no I don't really know any. Sorry.

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Excel Attendance Tracker Spreadsheet Formula Help

@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]]+1``

It 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".