Discharge Hydrograph with Excel?

[jeb6294]

We only have a couple computers here with programs that'll do a hydrograph for the discharge from a detention basin. Lucky for me they are both laptops that tend to be out of the office a lot.

I'm trying to do a hydrograph so I can determine the orifice size to drain a given volume over a 36 hour period...stupid water quality regs. I'll try and explain how I'm doing it now, but I'm not sure how well this'll work:

Right now I'm trying to do a spreadsheet in 0.10 hour increments. Based on the orifice size you're using, it figures the Q using q=ca(2gh)^0.5 and multiplies that cfs times 0.10 hours to get the volume of water that is dumped in that 0.10 hour period. After that volume is released, the water level goes down so it repeats this process with the new "h" for every 0.10 hr period until it gets to a level where the water level is at the centroid of my orifice. Once it hits the centroid, it starts doing the same thing but treats the orifice as a weir, q=clH^3/2 (for simplicity's sake I'm using a square orifice).

Hopefully you can follow all that...seems to work fine in theory, but it just doesn't want to work though. Even if I run the time out to 48 hours, it still won't go to a volume of 0 no matter what orifice size I use.

If it makes any difference: volume is 31,363 cf, basin is 42,000 sf with vertical sides.

 

[cvg]

you will never get to a volume of zero and you will have to be satisfied (when using this method) at just getting close. Bear in mind that with infiltration and evaporation, that last few inches will not drain through the pipe anyway - so don't worry about trying to simulate it. I usually stop the calculations when I get to about 90 - 95% drained. Also, it seems that you need to begin your weir equation at the top of the orifice, not at the centroid. This method will give you a fairly good estimate. I have seen developers submit and be approved using a single calculation of time based upon the average depth in the pond - with absolutely no iterations, so this method is far superior to that.

 

[BelspringKen]

I used an old DOS program a few years ago that did pretty much what you are doing with a spreadsheet. We generated the reports and got several jobs approved with it. The reports were not pretty but we did get them approved.

I am curious... how close to zero are you getting?

 

[jeb6294]

With a 4-inch square orifice, which is roughly what I figured it should be, I still have almost 4500cf left after 48 hours. At 36 hours it's still over 6300cf.

The more I look at it, it looks like the discharge will just out to infinity. The only way to ever reach a flow of 0 is when "h" finally reaches 0 and that will never actually happen.