countif

T

Thank you, Heath

Im trying to create a formula to go out and look at a date (DD/MM/YYYY) and
yield a Month and and year only instead of just a month as in =month(cell)
which only gives me the month...but Im going to need to tell when the yerar
turns to 2007.

any suggestions? I cant use the field with the whole date so Im having to
try to convert it to a month and year. And if possible I would also like for
it to tell me "N/A if the filed is blank. Im not sure if thats even
possible...

thanks guys
Heath
 
R

Ron Coderre

Try this:

For a date in A1

These formula yield a numeric value of YYYYMM
B1: =YEAR(A1)*100+MONTH(A1)
OR
B1: =--TEXT(A1,"YYYYMM")

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
T

Thank you, Heath

Actually it doesnt seem to be working.

I used the formula B1: =YEAR(I2)*100+MONTH(I2) or the date of 04/31/06 and
the answer came back 3/25/2449


So then I tried the formula B1: =--TEXT(I2,"YYYYMM") and the answer came
back the same thing....3/25/2449...

Am I doing soething simple wrong...Im just looking for the answer to be 4/06
for a field that has 4/31/06
 
D

Dave Peterson

A date of 4/31/2006 seems wrong to me.

And after you put a real date in I2 and format that cell with the
=YEAR(I2)*100+MONTH(I2)
formula as General, what did you see?
 
Top