hydrology water-year date conversion excel

  • Thread starter Thread starter Alexander Ford
  • Start date Start date
A

Alexander Ford

Has anyone found an easy way to relate dates (mm/dd/yy) in excel to
water-year days or simply days of the year for that matter? --Z
 
Hi Alexander!

To get the water year day of a date in A1:

=IF(A1<DATE(YEAR(A1),10,1),A1-DATE(YEAR(A1)-1,9,30),A1-DATE(YEAR(A1),9,30))

To get the day of year of a date in A1:

=A1-DATE(YEAR(A1)-1,12,31)


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/
 
Hi David!

I did a Google search and saw it related to Oct 1st as the beginning
of the Water Year. Again, my searches showed a US usage. I wondered if
there was such a concept here (possibly using Apr 1st) but couldn't
find any.

I assume it's based upon some constant start of a rainfall / river
flow cycle with Oct 1st being the start of the period when no water
flows off the mountains; but I'm only guessing!

If they use it a lot, they might use a UDF:

Function WATERDAY(WaterDate As Date) As Integer
If WaterDate < DateSerial(Year(WaterDate), 10, 1) Then
WATERDAY = WaterDate - DateSerial(Year(WaterDate) - 1, 9, 30)
Else
WATERDAY = WaterDate - DateSerial(Year(WaterDate), 9, 30)
End If
End Function
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/
 
David said:
When I saw "water year day" I thought it would relate to
India and water usage on a calendar different for each
village, but instead I find all references in Google to being
in the US with Oct 1st as marking the beginning of water year 2003.
But I can't find if that date changes from year to year.

http://ag.arizona.edu/AZWATER/awr/septoct02/vapors.html

http://il.water.usgs.gov/annual_report/misc/cdextion.htm

So where did you hear of this as you are in Australia.
Click to expand...
In the USA, we use Oct 1st of each & every year as the beginning of a
Water Year. Why it got started that way, I have no idea but probably
related to hte US Gov'ts Fiscal Year which does the same thing... In
other words, a US Water Year will always run from 10/01/xxxx 00:00:00 to
09/30/xxxx+1 23:59:59.

Jim Cornwall
(US Geological Survey Water Resources)
 
Back
Top