Solved

How to count the number of days in a column.

Posted on 2014-04-08
11
300 Views
Last Modified: 2014-04-10
All,

I have sheet that I need to count the number of days in a column. The first cell stays the same (D20) but the column gets bigger or smaller on any given day. I need to fill in how many day between today and the rest of the days in the column.  If you look at my sheet you will see what I need.

Thanks for the help!
sample.xlsx
0
Comment
Question by:Edward Pamias
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39987146
I looked at your file and I still don't understand. Please explain.
0
 
LVL 18

Author Comment

by:Edward Pamias
ID: 39987190
in the date column I need to count from todays date to the dates in the column. (excluding weekends and holidays in the formula)
For Example

4-8, 4-7, 4-4   in the box 1 to 2 days   I would put 3  (meaning 3 dates are 1 to 2 days from today)

4-1, 4-2, 4-3   in the box 3 to 5 days I would put 3   and so on for the rest of the boxes up top.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39987213
I guess I'm dense, I still don't get it. Please attach a file showing what you want.
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 18

Author Comment

by:Edward Pamias
ID: 39987264
See attached
sample.xlsx
0
 
LVL 18

Author Comment

by:Edward Pamias
ID: 39989911
I think I found the formula - I need to use NETWORKDAYS  but since the column changes daily.... how do I get it to work? Also I need to exclude weekends and holidays.
0
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39991112
Sorry for the delay.

You need to setup a helper column to count the days using the following formula (and drag down):
=IF(D20>TODAY(),NETWORKDAYS(TODAY(),D20),NETWORKDAYS(D20,TODAY()))

Open in new window


To count the number of occurrences of 0-2 use this formula:
=COUNTIF($F$20:$F$35,"<=2")

Open in new window


To count the number of occurrences of 3-5 use this formula:
=COUNTIFS(F20:F35,">2",F20:F35,"<6")

Open in new window


To count the number of occurrences of 6-10 use this formula:
=COUNTIFS($F$20:$F$35,">5",$F$20:$F$35,"<11")

Open in new window


To count the number of occurrences of larger than 11 use this formula:
=COUNTIF($F$20:$F$35,">=11")

Open in new window


This formula will give you the average:
=AVERAGE(F20:F35)

Open in new window


See attached file.
sample.xlsx
0
 
LVL 18

Author Comment

by:Edward Pamias
ID: 39991348
Thanks for all the formulas. Will the formulas work if my column size changes daily. My data may only go from F20 to F25 or it may be more like F20 to F40.... how do I make this dynamic?
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39991734
There are several methods to create dynamic named ranges, you will probably get a larger variety of answers if you post a new question. If you do want I can show you several methods.
0
 
LVL 18

Author Comment

by:Edward Pamias
ID: 39991777
I did mention about the dynamics of the columns in this question I did not say dynamic but that's what I meant.  

"I have sheet that I need to count the number of days in a column. The first cell stays the same (D20) but the column gets bigger or smaller on any given day. "


If I need to open another question about this I will.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39991978
You don't have to open a new question, but since there are many methods, chances are you will get more responses from other experts on a new question.

Anyway, the most simple method of all is to create a table (select the data then click Table on the Add tab), then in-place of the range put the table column name i.e.
=COUNTIF(Table1[helper column],"<=2")
=COUNTIFS(Table1[helper column],">2",Table1[helper column],"<6")
=COUNTIFS(Table1[helper column],">5",Table1[helper column],"<11")
=COUNTIF(Table1[helper column],">=11") 

Open in new window

sample.xlsx
0
 
LVL 18

Author Comment

by:Edward Pamias
ID: 39992207
Unfortunately, I cannot change the sheet I am working with. I can add the formulas but cannot modify it that way. I can open another question using these formulas. Thanks for the help!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question