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
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
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 as01-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 theDaysandMonthsand only display the result as ayear?
Basically, I need want to convert the data to a Date data-type without specifying days and months
excel vba date
edited Mar 8 at 7:37
Pᴇʜ
25.1k63052
asked Mar 7 at 22:37
RawrplusRawrplus
2,47941329
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 as01-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 theDaysandMonthsand only display the result as ayear?
Basically, I need want to convert the data to a Date data-type without specifying days and months
excel vba date
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 inyyyyformat. 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 aDatedata-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 toyyyy. 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 as01-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 theDaysandMonthsand only display the result as ayear?
Basically, I need want to convert the data to a Date data-type without specifying days and months
excel vba date
edited Mar 8 at 7:37
Pᴇʜ
25.1k63052
asked Mar 7 at 22:37
RawrplusRawrplus
2,47941329
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 as01-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 theDaysandMonthsand only display the result as ayear?
Basically, I need want to convert the data to a Date data-type without specifying days and months
excel vba date
excel vba date
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
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
edited Mar 8 at 7:37
Pᴇʜ
25.1k63052
25.1k63052
asked Mar 7 at 22:37
RawrplusRawrplus
2,47941329
asked Mar 7 at 22:37
RawrplusRawrplus
2,47941329
asked Mar 7 at 22:37
RawrplusRawrplus
2,47941329
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 inyyyyformat. 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 aDatedata-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 toyyyy. Then Copy paste value from B to A, get rid of column B.
– PatricK
Mar 7 at 22:50
add a comment |
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 inyyyyformat. 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 aDatedata-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 toyyyy. Then Copy paste value from B to A, get rid of column B.
– PatricK
Mar 7 at 22:50
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
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
@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 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
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
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 |
1 Answer
1
active
oldest
votes
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()
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 put01.01.2011in 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
);
);
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
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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()
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 put01.01.2011in 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()
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 put01.01.2011in 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()
answered Mar 7 at 22:44
VityataVityata
32.4k72453
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()
answered Mar 7 at 22:44
VityataVityata
32.4k72453
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
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 put01.01.2011in 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
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 put01.01.2011in 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
1
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
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
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
@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
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
@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 |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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
yyyyformat. 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
Datedata-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 toyyyy. Then Copy paste value from B to A, get rid of column B.– PatricK
Mar 7 at 22:50