Excel, date

C

Chris

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
 
J

Jezebel

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))
 
B

bpeltzer

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
 
S

Sandy Mann

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
[email protected]
Replace@mailinator with @tiscali.co.uk
 
S

Sandy Mann

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
[email protected]
Replace@mailinator with @tiscali.co.uk
 
N

nastech

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
 
B

Bob Phillips

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)
 
B

Bob Phillips

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)
 
N

nastech

Great.. thanks

Bob Phillips said:
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)
Click to expand...
 
S

Sandy Mann

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))>TODAY(),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))/((B16>D3)*2+3))*((B16>D3)*2+3),MONTH(B16),DAY(B16))>TODAY(),DATE(YEAR(B16)+INT((YEAR(TODAY())-YEAR(B16))/((B16>D3)*2+3))*((B16>D3)*2+3),MONTH(B16),DAY(B16)),DATE(YEAR(B16)+INT((YEAR(TODAY())-YEAR(B16))/((B16>D3)*2+3))*((B16>D3)*2+3)+((B16>D3)*2+3),MONTH(B16),DAY(B16)))

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk
 
R

Ron Rosenfeld

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

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
 
Top