Help with a date!

M

mx-3 for me

so my formula looks like this... which references another sheet for som
dates and increments every 24th row.
=OFFSET(Sheet1!B$6,((ROW(B9)-ROW(B$3))/24),0)
I need the date to read '04- but I dont want to change the othe
sheet's dates. Is there some IF statement I can construct to say tha
if the yy=03 then make the yy=04?
Im not familiar with IF statements in excel.
Thanks for your help in advanced!
Michell
 
H

Haggisdog

Michelle, if I understand your problem correctly, you should be able t
determine the date with an IF statement and the use of the Yea
function. The year function looks at a cell and determines only th
year in a four digit format. You could try the following formula t
determine if the cell was 2004 or 2003

=if(Year(cell_ref)=2003,"Do Something","Do Something else")

The cell_ref refers to the cell in your spreadsheet with the date, th
text that says "Do Something" can be replaced with whatever you want t
do if the year is 2003 and the "So Something Else" can be replaced wit
what you want to do if the cell is 2004.

If there are other possible years (say 2002 or 2001) then you migh
need a nested If statement but this should work for your immediat
needs.

If you need some help with what to do 'after' you've determined th
date then let me know.

Hope that helps.
Brya
 
M

mx-3 for me

I tried this...
=if(Year(Sheet1!B$6)=2003,Year=2004(OFFSET(Sheet1!B$6,((ROW(B4)-ROW(B$3))/24),0)),0)

But apparently it doesnt work.
I still want it to do what the OFFSET function does- only change th
year to 04 instead of 03.
There is no "than do something else" in this because all of the value
will be in 2003 and will need to be changed over to 2004.

Any ideas?
Thanks for your help by the way Bryan
 
H

Haggisdog

Well, what you're doing is seems pretty straightforward, could yo
provide some sample values for the offset function so I can write
formula that will increment the year for you. If you have a sampl
spreadsheet you can upload I can just update it and explain what I did
 
M

mx-3 for me

Wow you would goto all of that work for me? thats so nice!
Hmm leme see- the cells im referencing- B6 on sheet1 is

1-Jan-03
2-Jan-03
3-Jan-03
And so on and so fourth
B4 and B3 have nothing really- not even sure why those are in there.
But anyway... those are my values
 
H

Haggisdog

Sure, no problem. I have a short meeting I have to go to but I'll work
up a formula for you when I get back. Out of curiousity what values
are in B3 and B4 and are you copying this formula down a series of rows
in a column?
 
M

mx-3 for me

B3 = =OFFSET(Sheet1!B$6,((ROW(B4)-ROW(B$3))/24),0)
and B4 = =OFFSET(Sheet1!B$6,((ROW(B5)-ROW(B$3))/24),0)

they are the same thing im filling the date all the way down th
column. Every 24th value incremements the reference to the sheet1. s
the 24th cell will be referencing Sheet1!B$7

I needed to have every hour of everyday for the whole year fille
through- but didnt really feel like writing the date out 365*24 time
lol
pretty crazy
 
D

Dave Peterson

I put a today's date in A1 and used this formula:

=TEXT(YEAR(A1)+1,"yy-")

It returned:
05-

Maybe you could surround your existing formula with this formula:

=TEXT(YEAR(OFFSET(Sheet1!B$6,((ROW(B9)-ROW(B$3))/24),0))+1,"yy-")

(Untested in a real workbook)
 
Top