General discussion

Locked

How to exclude weekends when counting da

By scottomlinson ·
In Excel I have a need to count the days from today back to a cell reference and then use that value to divide into a count of records (records per day...). What I can't seem to figure out is how to count only the weekdays in this range since records are not created on weekends. Any ideas?

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

How to exclude weekends when counting da

by mightyduk In reply to How to exclude weekends w ...

If you have named range records and named range days:

sum(records)/(count(days)*5/7)

If you have one range of fields with the quantity of records for each day, you could simply use:

sum(records)/count(records)

If this doesn't answer the question, you should clarify what the layout of the spreadsheet is.

Collapse -

How to exclude weekends when counting da

by scottomlinson In reply to How to exclude weekends w ...

Thank you for your input. If it were not for the answer from Larry H. I would use yours. Thanks.

Collapse -

How to exclude weekends when counting da

by Larry H. In reply to How to exclude weekends w ...

You can also load in the Analysis Toolpack, which contains a function that can do this for you. It is called =NETWORKDAYS(Start_Date, End_Date) and has an option to also exclude holidays if you need to.

Go to TOOLS, ADD-INS.
Choose the ANALYSIS TOOLPACK and click OK (You'll probably need your Office CD at this point).

See Excel's Help files for more info.

Collapse -

How to exclude weekends when counting da

by scottomlinson In reply to How to exclude weekends w ...

This is teriffic! Thanks for the timely help.

Back to Desktop Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums