Ufdjrw

Can I run a new neutral wire to repair a broken circuit?

CAST throwing error when run in stored procedure but not when run as raw query

Bullying boss launched a smear campaign and made me unemployable

How dangerous is XSS?

How do I handle a potential work/personal life conflict as the manager of one of my friends?

How to write generic function with two inputs?

Intersection Puzzle

Why didn't Boeing produce its own regional jet?

How could indestructible materials be used in power generation?

What method can I use to design a dungeon difficult enough that the PCs can't make it through without killing them?

How do I deal with an unproductive colleague in a small company?

Why do bosons tend to occupy the same state?

Why would the Red Woman birth a shadow if she worshipped the Lord of the Light?

Valid term from quadratic sequence?

How much of data wrangling is a data scientist's job?

How seriously should I take size and weight limits of hand luggage?

How to prevent "they're falling in love" trope

Are there any examples of a variable being normally distributed that is *not* due to the Central Limit Theorem?

Do scales need to be in alphabetical order?

Mathematica command that allows it to read my intentions

Personal Teleportation: From Rags to Riches

What type of content (depth/breadth) is expected for a short presentation for Asst Professor interview in the UK?

What mechanic is there to disable a threat instead of killing it?

What does “the session was packed” mean in this context?

How to display only year in vba?

VBA doesn't return the correct Date with Now()How do I use PHP to get the current year?How to return only the Date from a SQL Server DateTime datatypeHow do you get a timestamp in JavaScript?How do I get the current date in JavaScript?How to format a JavaScript dateGet the current year in JavaScriptHow to avoid using Select in Excel VBAInput month as an integer, output the final day of that monthExcel Formula or VBA to convert year, month data to actual dateConvert date format in Excel from: Month Date, Year to yyyy-mm-dd

0

I have the following data set in general formatting.

Bunch of years with no day nor months provided.

What I want to achieve, is to display the date in yyyy format.

I made a standard DateSerial function to convert the data to date properly.

Private Function toDate(ByVal text As String) As Date
 toDate = DateSerial(Int(text), 1, 1)
End Function

For Each cell in ws.Range("A21:A" & lr)
 cell = toDate(cell)
 cell.NumberFormat = "yyyy"
Next cell

Now, technically this works, but if you click inside the cell, it
obviously shows the date only as 01-01-year - that is to be expected
with usage of dateserial

But I was wondering, would it be possible given my data to somehow
omit the Days and Months and only display the result as a year?

Basically, I need want to convert the data to a Date data-type without specifying days and months

excel vba date

share|improve this question

edited Mar 8 at 7:37

Pᴇʜ

25.1k63052

asked Mar 7 at 22:37

RawrplusRawrplus

2,47941329

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What's the end goal here? If you always want them displayed as xxxx even when the user clicks to edit, why not just us xxxx ?
  
  – Tim Williams
  Mar 7 at 22:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @TimWilliams Sadly, this was requested specifically to be in yyyy format. I personally would have also preferred to leave it as a simple number, but not my choice here :)
  
  – Rawrplus
  Mar 7 at 22:41
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I guess, the question could have been shortened to a simple "Is it possible to create a Date data-type without day and month", but you know, "having to show your effort and all that" being applied
  
  – Rawrplus
  Mar 7 at 22:43
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I would guess the answer is No, and that if it's important for the list to stay as xxxx then conversion to a date should occur when the values are consumed/used.
  
  – Tim Williams
  Mar 7 at 22:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You can do with Excel formula + change Number Format. Say your Year series is A1, then in B1: =DATE(A1,1,1), fill down and you are done, then change number format both A:B columns to yyyy. Then Copy paste value from B to A, get rid of column B.
  
  – PatricK
  Mar 7 at 22:50
  
  

  
  
  
  

add a comment | 

0

I have the following data set in general formatting.

Bunch of years with no day nor months provided.

What I want to achieve, is to display the date in yyyy format.

I made a standard DateSerial function to convert the data to date properly.

Private Function toDate(ByVal text As String) As Date
 toDate = DateSerial(Int(text), 1, 1)
End Function

For Each cell in ws.Range("A21:A" & lr)
 cell = toDate(cell)
 cell.NumberFormat = "yyyy"
Next cell

Now, technically this works, but if you click inside the cell, it
obviously shows the date only as 01-01-year - that is to be expected
with usage of dateserial

But I was wondering, would it be possible given my data to somehow
omit the Days and Months and only display the result as a year?

Basically, I need want to convert the data to a Date data-type without specifying days and months

excel vba date

share|improve this question

edited Mar 8 at 7:37

Pᴇʜ

25.1k63052

asked Mar 7 at 22:37

RawrplusRawrplus

2,47941329

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What's the end goal here? If you always want them displayed as xxxx even when the user clicks to edit, why not just us xxxx ?
  
  – Tim Williams
  Mar 7 at 22:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @TimWilliams Sadly, this was requested specifically to be in yyyy format. I personally would have also preferred to leave it as a simple number, but not my choice here :)
  
  – Rawrplus
  Mar 7 at 22:41
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I guess, the question could have been shortened to a simple "Is it possible to create a Date data-type without day and month", but you know, "having to show your effort and all that" being applied
  
  – Rawrplus
  Mar 7 at 22:43
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I would guess the answer is No, and that if it's important for the list to stay as xxxx then conversion to a date should occur when the values are consumed/used.
  
  – Tim Williams
  Mar 7 at 22:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You can do with Excel formula + change Number Format. Say your Year series is A1, then in B1: =DATE(A1,1,1), fill down and you are done, then change number format both A:B columns to yyyy. Then Copy paste value from B to A, get rid of column B.
  
  – PatricK
  Mar 7 at 22:50
  
  

  
  
  
  

add a comment | 

I have the following data set in general formatting.

Bunch of years with no day nor months provided.

What I want to achieve, is to display the date in yyyy format.

I made a standard DateSerial function to convert the data to date properly.

Private Function toDate(ByVal text As String) As Date
 toDate = DateSerial(Int(text), 1, 1)
End Function

For Each cell in ws.Range("A21:A" & lr)
 cell = toDate(cell)
 cell.NumberFormat = "yyyy"
Next cell

Now, technically this works, but if you click inside the cell, it
obviously shows the date only as 01-01-year - that is to be expected
with usage of dateserial

But I was wondering, would it be possible given my data to somehow
omit the Days and Months and only display the result as a year?

Basically, I need want to convert the data to a Date data-type without specifying days and months

excel vba date

share|improve this question

edited Mar 8 at 7:37

Pᴇʜ

25.1k63052

asked Mar 7 at 22:37

RawrplusRawrplus

2,47941329

Private Function toDate(ByVal text As String) As Date
 toDate = DateSerial(Int(text), 1, 1)
End Function

For Each cell in ws.Range("A21:A" & lr)
 cell = toDate(cell)
 cell.NumberFormat = "yyyy"
Next cell

share|improve this question

edited Mar 8 at 7:37

Pᴇʜ

25.1k63052

asked Mar 7 at 22:37

RawrplusRawrplus

2,47941329

share|improve this question

edited Mar 8 at 7:37

Pᴇʜ

25.1k63052

asked Mar 7 at 22:37

RawrplusRawrplus

2,47941329

edited Mar 8 at 7:37

Pᴇʜ

25.1k63052

edited Mar 8 at 7:37

Pᴇʜ

25.1k63052

asked Mar 7 at 22:37

RawrplusRawrplus

2,47941329

asked Mar 7 at 22:37

RawrplusRawrplus

2,47941329

1 Answer
1

active

oldest

votes

1

Entering the years and formatting the date as "YYYY" could be done like this:

Sub TestMe()

 With ThisWorkbook.Worksheets(1)
 .Range("A1") = 2011
 .Range("A2") = 2013
 .Range("A3") = 2011

 Dim myCell As Range
 For Each myCell In .Range("A1:A3")
 myCell = DateSerial(myCell, 1, 1)
 myCell.NumberFormat = "yyyy"
 Next myCell
 End With

End Sub

The "trick", is that DateSerial(myCell, 1, 1) takes the year from the cell and adds 1 for day and January for month. Thus, every year is represented as the date 1.January from that year.

More about the Date in VBA and Excel - VBA doesn't return the correct Date with Now()

share|improve this answer

edited Mar 7 at 22:50

answered Mar 7 at 22:44

VityataVityata

32.4k72453

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I think the issue is that they want only "2011", "2013", or "2011" to appear in the formula bar when a user selects the cell, instead of "m-d-yyyy".
  
  – dwirony
  Mar 7 at 22:47
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  As @dwirony pointed out above ^ ... I guess i'll remove the 1905 part as it's just useless clutter in the question
  
  – Rawrplus
  Mar 7 at 22:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Rawrplus - well, the easiest way I can think of is to use the formula =YEAR(A1) and put 01.01.2011 in A1.
  
  – Vityata
  Mar 7 at 22:50
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @Vityata yeah I guess that kinda hides the day and month. I'll leave the question open to see if anyone comes up with anything, but yeah, most likely it is not possible to omit day and motnh out of the date
  
  – Rawrplus
  Mar 7 at 22:53
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55053930%2fhow-to-display-only-year-in-vba%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

Entering the years and formatting the date as "YYYY" could be done like this:

Sub TestMe()

 With ThisWorkbook.Worksheets(1)
 .Range("A1") = 2011
 .Range("A2") = 2013
 .Range("A3") = 2011

 Dim myCell As Range
 For Each myCell In .Range("A1:A3")
 myCell = DateSerial(myCell, 1, 1)
 myCell.NumberFormat = "yyyy"
 Next myCell
 End With

End Sub

The "trick", is that DateSerial(myCell, 1, 1) takes the year from the cell and adds 1 for day and January for month. Thus, every year is represented as the date 1.January from that year.

More about the Date in VBA and Excel - VBA doesn't return the correct Date with Now()

share|improve this answer

edited Mar 7 at 22:50

answered Mar 7 at 22:44

VityataVityata

32.4k72453

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I think the issue is that they want only "2011", "2013", or "2011" to appear in the formula bar when a user selects the cell, instead of "m-d-yyyy".
  
  – dwirony
  Mar 7 at 22:47
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  As @dwirony pointed out above ^ ... I guess i'll remove the 1905 part as it's just useless clutter in the question
  
  – Rawrplus
  Mar 7 at 22:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Rawrplus - well, the easiest way I can think of is to use the formula =YEAR(A1) and put 01.01.2011 in A1.
  
  – Vityata
  Mar 7 at 22:50
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @Vityata yeah I guess that kinda hides the day and month. I'll leave the question open to see if anyone comes up with anything, but yeah, most likely it is not possible to omit day and motnh out of the date
  
  – Rawrplus
  Mar 7 at 22:53
  

  
  
  
  

add a comment | 

1

Entering the years and formatting the date as "YYYY" could be done like this:

Sub TestMe()

 With ThisWorkbook.Worksheets(1)
 .Range("A1") = 2011
 .Range("A2") = 2013
 .Range("A3") = 2011

 Dim myCell As Range
 For Each myCell In .Range("A1:A3")
 myCell = DateSerial(myCell, 1, 1)
 myCell.NumberFormat = "yyyy"
 Next myCell
 End With

End Sub

The "trick", is that DateSerial(myCell, 1, 1) takes the year from the cell and adds 1 for day and January for month. Thus, every year is represented as the date 1.January from that year.

More about the Date in VBA and Excel - VBA doesn't return the correct Date with Now()

share|improve this answer

edited Mar 7 at 22:50

answered Mar 7 at 22:44

VityataVityata

32.4k72453

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I think the issue is that they want only "2011", "2013", or "2011" to appear in the formula bar when a user selects the cell, instead of "m-d-yyyy".
  
  – dwirony
  Mar 7 at 22:47
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  As @dwirony pointed out above ^ ... I guess i'll remove the 1905 part as it's just useless clutter in the question
  
  – Rawrplus
  Mar 7 at 22:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Rawrplus - well, the easiest way I can think of is to use the formula =YEAR(A1) and put 01.01.2011 in A1.
  
  – Vityata
  Mar 7 at 22:50
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @Vityata yeah I guess that kinda hides the day and month. I'll leave the question open to see if anyone comes up with anything, but yeah, most likely it is not possible to omit day and motnh out of the date
  
  – Rawrplus
  Mar 7 at 22:53
  

  
  
  
  

add a comment | 

Entering the years and formatting the date as "YYYY" could be done like this:

Sub TestMe()

 With ThisWorkbook.Worksheets(1)
 .Range("A1") = 2011
 .Range("A2") = 2013
 .Range("A3") = 2011

 Dim myCell As Range
 For Each myCell In .Range("A1:A3")
 myCell = DateSerial(myCell, 1, 1)
 myCell.NumberFormat = "yyyy"
 Next myCell
 End With

End Sub

The "trick", is that DateSerial(myCell, 1, 1) takes the year from the cell and adds 1 for day and January for month. Thus, every year is represented as the date 1.January from that year.

More about the Date in VBA and Excel - VBA doesn't return the correct Date with Now()

share|improve this answer

edited Mar 7 at 22:50

answered Mar 7 at 22:44

VityataVityata

32.4k72453

Sub TestMe()

 With ThisWorkbook.Worksheets(1)
 .Range("A1") = 2011
 .Range("A2") = 2013
 .Range("A3") = 2011

 Dim myCell As Range
 For Each myCell In .Range("A1:A3")
 myCell = DateSerial(myCell, 1, 1)
 myCell.NumberFormat = "yyyy"
 Next myCell
 End With

End Sub

share|improve this answer

edited Mar 7 at 22:50

answered Mar 7 at 22:44

VityataVityata

32.4k72453

answered Mar 7 at 22:44

VityataVityata

32.4k72453

answered Mar 7 at 22:44

VityataVityata

32.4k72453