Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Excel Query Wizard Date Format | Excel Discussion (Misc queries) | |||
Excel file modification date | Excel Discussion (Misc queries) | |||
Excel formula with date constraints | Excel Discussion (Misc queries) | |||
In Excel 2003, entering date without slashes, the date is incorre. | Excel Discussion (Misc queries) |