asked on

How to count the number of days in a column.

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

Joe Howard

I looked at your file and I still don't understand. Please explain.

Edward Pamias

ASKER

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.

Joe Howard

I guess I'm dense, I still don't get it. Please attach a file showing what you want.

Edward Pamias

ASKER

See attached
sample.xlsx

Edward Pamias

ASKER

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.

ASKER CERTIFIED SOLUTION

Joe Howard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Edward Pamias

ASKER

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?

Joe Howard

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.

Edward Pamias

ASKER

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.

Joe Howard

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

Edward Pamias

ASKER

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!