Date Format in VBA

[HCG]

Hi All,
I'm trying to change the format of a date to include either CY or FY at the beginning of a date depending on a user input using VBA. The issue I run into is when I use Y (in CY for example), VBA recognises it as yy and returns the year when what I really want is just to include the text CY. The code I'm using is

cell.NumberFormat = "CY yyyy"

This returns C22 as the year being referred to in the cell is 2022.

Can anyone please help? Thanks.

 

[Peter_SSs]

Welcome to the MrExcel board!

Does this do what you want?
cell.NumberFormat = "C\Y yyyy"

 

[HCG]

Works perfect. Many thanks.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

The \ tells excel to treat the Y as a literal character rather than the normal date formatting meaning of Y being "year"