Year date format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to extract the year only in a cell from the date in another cell.
What I have tried is:

Range("J4").Select
ActiveCell.FormulaR1C1 = "=text(B4, "yyyy")"
Range("B3").Select

I get an error which reads: Compile error:
 
I somehow lost the entire macro in the file I was working on. I now have to
wait until our IT people can restore it to try your resolution to the
problem. I will post a 'Yes' or 'No' once I have tested my document.

Thanks for the quick response.
 
Hi,

I input the line exactly as shown below, then run my macro. The results I
get are the cell shows: #NAME?

When I look at the actual formula it appears as: =YEAR('B4').
How do I get rid of the single quote around B4?

Thanks
 
Zone used: Activecell.Formula = ...
I suspect you're still using: ActiveCell.FormulaR1C1 = ...

Try using Activecell.Formula instead.
 
I did not leave a space between ActiveCell.Formula="YEAR(B4)". I tried it
again, with a space. The results I obtained in the cell were 1905. The
exact code I am using is as follows:

Range("B4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"

Range("J4").Select
ActiveCell.Formula = "=YEAR(B4)"

As stated, this gives me a result of 1905 in cell J4.

Thanks for your help
 
I just posted a reply indicating there was still a problem - I just figured
it out. I had formatted cell J4 to Custom formatting as 'yyyy'. When I ran
the macro with

Range("J4").Select
ActiveCell.Formula = "=YEAR(B4)"

I was getting the result of 1905. I have now formatted the cell as number
and now get the correct response.

Thanks for all you assistance.
Linda
 
Glad you got it fixed.

You can accomplish the same kind of thing without the .select's:

Range("B4").FormulaR1C1 = "=TODAY()"
Range("J4").Formula = "=YEAR(B4)"

or even

with worksheets("Somesheetnamehere")
.Range("B4").Formula = "=TODAY()"
with .range("J4")
.numberformat = "General"
.Formula = "=YEAR(B4)"
end with
end with
 
Back
Top