hydrology water-year date conversion excel

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
 
N

Norman Harker

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
[email protected]

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/
 
D

David McRitchie

N

Norman Harker

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
[email protected]

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/
 
J

J. F. Cornwall

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Norman Harker said:
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
[email protected]

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/
Click to expand...
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)
 
Top