Excel, date
 

I have on cell that has an original manfg. date 1/1/90 (this date will
change) the next hydro date is either 3 or 5 years depending on the date.
prior to 1/1/91 cylinder must be done every three years and after 1/1/91 they
must be done every 5 years. I am stuck on the formula asd I have it now
=IF($D$3<B16,1095+D3,1825+D3) any assitance would be of great help.
Thnaks

Excel, date
 

Using named cells:

Manfg_Date: [any date]
Ref_Date: 1-Jan-91
Interval: =IF(Manfg_Date<Ref_Date,3,5)
Last_Hydro: [any date]
Next_Hydro: =Date(Year(Last_Hydro)+Interval, Month(Last_Hydro),
Day(Last_Hydro))



"Chris" wrote in message
...
I have on cell that has an original manfg. date 1/1/90 (this date will
change) the next hydro date is either 3 or 5 years depending on the date.
prior to 1/1/91 cylinder must be done every three years and after 1/1/91
they
must be done every 5 years. I am stuck on the formula asd I have it now
=IF($D$3<B16,1095+D3,1825+D3) any assitance would be of great help.
Thnaks

Excel, date
 

It looks like you may confusing which cell has the cutoff date (1/1/91) and
which has the original mfg date. I'll assume that $D$3 is the cutoff date
which won't change, and B16 is the original mfg date. Then the formula for
the first hydro date would be =if(b16<$d$3,1095,1825)+b16.
My guess is that each row will actually have both an original mfg date and a
latest hydro date. If those are in b16 and c16, respectively, then the next
hydro date would be =if(b16<$d$3,1095,1825)+c16.
--Bruce

"Chris" wrote:

I have on cell that has an original manfg. date 1/1/90 (this date will
change) the next hydro date is either 3 or 5 years depending on the date.
prior to 1/1/91 cylinder must be done every three years and after 1/1/91 they
must be done every 5 years. I am stuck on the formula asd I have it now
=IF($D$3<B16,1095+D3,1825+D3) any assitance would be of great help.
Thnaks

Excel, date
 

Chris,

If I understand you correctly, try:

=DATE(YEAR(D3)+(D3=33239)*2+3,MONTH(D3),DAY(D3))

(33239 is the "Day No" of 1/1/91)

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Chris" wrote in message
...
I have on cell that has an original manfg. date 1/1/90 (this date will
change) the next hydro date is either 3 or 5 years depending on the date.
prior to 1/1/91 cylinder must be done every three years and after 1/1/91
they
must be done every 5 years. I am stuck on the formula asd I have it now
=IF($D$3<B16,1095+D3,1825+D3) any assitance would be of great help.
Thnaks

Excel, date
 

Having read Bruce's post, in my formula I assumed that D3 was the original
manfg date.

Also, before Halan point out to me, (again!) that 33239 is only the "Day No"
in Excel's default date system, in the 1904 Date system it would be 31777.
(I never use the 1904 date system so I keep forgetting that there are two
systems)

To remove any confusion use:

=DATE(YEAR(D3)+(D3=DATE(1991,MONTH(1),DAY(1)))*2+ 3,MONTH(D3),DAY(D3))

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Chris,

If I understand you correctly, try:

=DATE(YEAR(D3)+(D3=33239)*2+3,MONTH(D3),DAY(D3))

(33239 is the "Day No" of 1/1/91)

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Chris" wrote in message
...
I have on cell that has an original manfg. date 1/1/90 (this date will
change) the next hydro date is either 3 or 5 years depending on the date.
prior to 1/1/91 cylinder must be done every three years and after 1/1/91
they
must be done every 5 years. I am stuck on the formula asd I have it now
=IF($D$3<B16,1095+D3,1825+D3) any assitance would be of great help.
Thnaks

Excel, date
 

Hi, I have a date question was trying 2 ask, 2 posts down, getting a feel of
what to ask.. don't get date functions too much.
How do you backward / forward find date serial for 1900 system, to figure
equations (maybe just plug into a cell, see what it says: struggling with
that too.

Equation Looking for: How do I determine if date in a cell is 1 day old.
Thanks.

Something like: ?
=IF(DATE(DAY(A1)+1<DATE(TODAY(A1)),true,false) guessing something like this



"Sandy Mann" wrote:

Chris,

If I understand you correctly, try:

=DATE(YEAR(D3)+(D3=33239)*2+3,MONTH(D3),DAY(D3))

(33239 is the "Day No" of 1/1/91)

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk

Excel, date
 

1 day old

=IF(A1+1 = TODAY(),"yes","no")

older than today

=IF(A1<TODAY(),"yes","no")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nastech" wrote in message
...
Hi, I have a date question was trying 2 ask, 2 posts down, getting a feel
of
what to ask.. don't get date functions too much.
How do you backward / forward find date serial for 1900 system, to figure
equations (maybe just plug into a cell, see what it says: struggling with
that too.

Equation Looking for: How do I determine if date in a cell is 1 day old.
Thanks.

Something like: ?
=IF(DATE(DAY(A1)+1<DATE(TODAY(A1)),true,false) guessing something like
this



"Sandy Mann" wrote:

Chris,

If I understand you correctly, try:

=DATE(YEAR(D3)+(D3=33239)*2+3,MONTH(D3),DAY(D3))

(33239 is the "Day No" of 1/1/91)

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk

Excel, date
 

Here is my attempt, assuming you want the next hydro date after today

=DATE(YEAR(B16)+(YEAR(TODAY())-YEAR($D$3))+IF($D$3<--"1991-01-01",CHOOSE(MOD
(YEAR(TODAY())-YEAR($D$3),3)+1,0,2,1),CHOOSE(MOD(YEAR(TODAY())-YEAR($D$3),5)
+1,0,4,3,2,1)),MONTH(B16),DAY(B16))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chris" wrote in message
...
I have on cell that has an original manfg. date 1/1/90 (this date will
change) the next hydro date is either 3 or 5 years depending on the date.
prior to 1/1/91 cylinder must be done every three years and after 1/1/91
they
must be done every 5 years. I am stuck on the formula asd I have it now
=IF($D$3<B16,1095+D3,1825+D3) any assitance would be of great help.
Thnaks

Excel, date
 

Great.. thanks

"Bob Phillips" wrote:

1 day old

=IF(A1+1 = TODAY(),"yes","no")

older than today

=IF(A1<TODAY(),"yes","no")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nastech" wrote in message
...
Hi, I have a date question was trying 2 ask, 2 posts down, getting a feel
of
what to ask.. don't get date functions too much.
How do you backward / forward find date serial for 1900 system, to figure
equations (maybe just plug into a cell, see what it says: struggling with
that too.

Equation Looking for: How do I determine if date in a cell is 1 day old.
Thanks.

Something like: ?
=IF(DATE(DAY(A1)+1<DATE(TODAY(A1)),true,false) guessing something like
this



"Sandy Mann" wrote:

Chris,

If I understand you correctly, try:

=DATE(YEAR(D3)+(D3=33239)*2+3,MONTH(D3),DAY(D3))

(33239 is the "Day No" of 1/1/91)

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk

Excel, date
 

Bob,

I see your point about the next hydro date after today.

With 1/1/1995 in B16, your formula returned 1/1/2010 for me, (as it should),
but when B16 was 1/12/1995 it returned 1/12/2010 when I reckon it should be
1/12/2005 being as it is not yet December. Also 1/1/89 returns 1/1/2004 -
unless I'm doing something wrong.

My (all day!) effort is:

=IF(DATE(YEAR(B16)+Years,MONTH(B16),DAY(B16))TODA Y(),DATE(YEAR(B16)+Years,MONTH(B16),DAY(B16)),DATE (YEAR(B16)+Years+Period,MONTH(B16),DAY(B16)))

where Years and Period are defined Names of:
Period =(!$B$16!$D$3)*2+3
and
Years =INT((YEAR(TODAY())-YEAR(!$B$16))/Period * Period

Without the Defined Names it becomes an unwieldy:

=IF(DATE(YEAR(B16)+INT((YEAR(TODAY())-YEAR(B16))/((B16D3)*2+3))*((B16D3)*2+3),MONTH(B16),DAY(B16) )TODAY(),DATE(YEAR(B16)+INT((YEAR(TODAY())-YEAR(B16))/((B16D3)*2+3))*((B16D3)*2+3),MONTH(B16),DAY(B16) ),DATE(YEAR(B16)+INT((YEAR(TODAY())-YEAR(B16))/((B16D3)*2+3))*((B16D3)*2+3)+((B16D3)*2+3),MONT H(B16),DAY(B16)))

--
Regards

Sandy

Replace@mailinator with @tiscali.co.uk


"Bob Phillips" wrote in message
...
Here is my attempt, assuming you want the next hydro date after today

=DATE(YEAR(B16)+(YEAR(TODAY())-YEAR($D$3))+IF($D$3<--"1991-01-01",CHOOSE(MOD
(YEAR(TODAY())-YEAR($D$3),3)+1,0,2,1),CHOOSE(MOD(YEAR(TODAY())-YEAR($D$3),5)
+1,0,4,3,2,1)),MONTH(B16),DAY(B16))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chris" wrote in message
...
I have on cell that has an original manfg. date 1/1/90 (this date will
change) the next hydro date is either 3 or 5 years depending on the date.
prior to 1/1/91 cylinder must be done every three years and after 1/1/91
they
must be done every 5 years. I am stuck on the formula asd I have it now
=IF($D$3<B16,1095+D3,1825+D3) any assitance would be of great help.
Thnaks

Excel, date
 

On Sun, 13 Nov 2005 15:11:01 -0800, "Chris"
wrote:

I have on cell that has an original manfg. date 1/1/90 (this date will
change) the next hydro date is either 3 or 5 years depending on the date.
prior to 1/1/91 cylinder must be done every three years and after 1/1/91 they
must be done every 5 years. I am stuck on the formula asd I have it now
=IF($D$3<B16,1095+D3,1825+D3) any assitance would be of great help.
Thnaks

You can write a User Defined Function in VBA that will do this.

<alt<F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

You can then use the formula

=NextHydro(mfg_date) to compute the next hydro date.

============================
Option Explicit

Function NextHydro(Mfg_date) As Date
Dim Three_Five As Date
Dim Intvl As Double

Three_Five = DateSerial(1990, 12, 31)

If Mfg_date Three_Five Then
Intvl = 5
Else: Intvl = 3
End If

NextHydro = DateAdd("yyyy", Intvl, Mfg_date)

Do Until NextHydro = Date
NextHydro = DateAdd("yyyy", Intvl, NextHydro)
Loop
End Function
================================

--ron