Counting days within a given date range

• Hi all,

I have a start date and an end date which may be any date from start 2001 to end 2005. These date ranges signify durations of projects.

Now, I want to find out how much money I made on these projects between 1/7/04 and 30/6/5.

I know how to work out the price per day. I know how to count the total number of days for each project. What I can't figure out is how to count the number of days during the period that I'm interested in.

Any suggestions?

Many thanks.

Zazie.

• Re: Counting days within a given date range

It depends... If it is a one time project you might just use the autofilters. Although I am sure there is a neater/easier way to do it since I am certainly not the most advanced on this forum (there are a few that amaze me here) Something like this might work for code.

Code
``````dim rngX as range,ws as worksheet,lngRow as long,decCount as decimal
ws.usedrange.calculate
lngrow=ws.cells.specialcells(xllastcell).row
for each rngX in ws.range("a2:a" & lngRow)
if rngX.value=> **your bottom serial date** and rngx.value<=**top serial date** then
decCount=deccount+rngX.offset(0,2) 'the 2 should equal the number of rows to the right or left of rngx
end if
next``````

Thanks
--Doug

• Re: Counting days within a given date range

Thanks ever so much Doug. I bottled out and did it with a series of columns and If functions and calculations in the end i.e. figured out If(start date < 30/6/04, start date, 30/6/04) then similar with end date, then worked out no. of days that fell within the period of interest, then figured out the cost for these days.

The forum is brill - I'm always delighted with people's suggestions.

Thanks again.

• Re: Counting days within a given date range

Zazie:
I do similar work using the NETWORKDAYS function in a number of scheduling/tracking worksheets in XL. This works best for me because it accepts variable holidays (non-working days) in addition to the start/end date and automatically excludes weekends. Below is partial text from Ms Help on this topic - FYI.

from Microsoft XL Help...
Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.
If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.

HTH

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!