Need to have Date format as dd.mm.yyyy for column using excel vba
up vote
0
down vote
favorite
I am new to Excel vba.. Currently working on creating macro for internal purpose where i manipulating the Date in that.
Currently i am Entering the date in Text Box and further then converted to date in required format using known function "cell.numberformat="dd.mm.yyyy"".
However still date is displayed as per local setting say "11/22/2018" in the formula bar instead of displaying "22.11.2018"
problem we further import the excel to SAP, there it assumes date format from formula bar and hence its not recognized in our SAP system.
Could you please let us know is there an any way to have date format same in column as well as in Formula bar.
excel date
asked Nov 22 at 7:20
Guru Prasaath
12
add a comment |
up vote
0
down vote
favorite
I am new to Excel vba.. Currently working on creating macro for internal purpose where i manipulating the Date in that.
Currently i am Entering the date in Text Box and further then converted to date in required format using known function "cell.numberformat="dd.mm.yyyy"".
However still date is displayed as per local setting say "11/22/2018" in the formula bar instead of displaying "22.11.2018"
problem we further import the excel to SAP, there it assumes date format from formula bar and hence its not recognized in our SAP system.
Could you please let us know is there an any way to have date format same in column as well as in Formula bar.
excel date
asked Nov 22 at 7:20
Guru Prasaath
12
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I am new to Excel vba.. Currently working on creating macro for internal purpose where i manipulating the Date in that.
Currently i am Entering the date in Text Box and further then converted to date in required format using known function "cell.numberformat="dd.mm.yyyy"".
However still date is displayed as per local setting say "11/22/2018" in the formula bar instead of displaying "22.11.2018"
problem we further import the excel to SAP, there it assumes date format from formula bar and hence its not recognized in our SAP system.
Could you please let us know is there an any way to have date format same in column as well as in Formula bar.
excel date
asked Nov 22 at 7:20
Guru Prasaath
12
I am new to Excel vba.. Currently working on creating macro for internal purpose where i manipulating the Date in that.
Currently i am Entering the date in Text Box and further then converted to date in required format using known function "cell.numberformat="dd.mm.yyyy"".
However still date is displayed as per local setting say "11/22/2018" in the formula bar instead of displaying "22.11.2018"
problem we further import the excel to SAP, there it assumes date format from formula bar and hence its not recognized in our SAP system.
Could you please let us know is there an any way to have date format same in column as well as in Formula bar.
excel date
excel date
asked Nov 22 at 7:20
Guru Prasaath
12
asked Nov 22 at 7:20
Guru Prasaath
12
edited Nov 22 at 7:26
asked Nov 22 at 7:20
Guru Prasaath
12
asked Nov 22 at 7:20
Guru Prasaath
12
asked Nov 22 at 7:20
Guru Prasaath
12
12
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
The date format dd.mm.yyyy is not recognised as a date by Excel. You would have therefore to change the contents of the cell to a string representation of the date. This would mess up any formulas that were doing any calculations on those dates (such as number of days btweeen dates etc). If you want to go down this route then this macro will convert all selected cells into text with the desired formula:
Sub ConvertDates
Dim r as range
For each r in selection
r.formula = format(r.value,"dd.mm.yyyy")
next r
end sub
answered Nov 22 at 14:54
Harassed Dad
2,5661511
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
The date format dd.mm.yyyy is not recognised as a date by Excel. You would have therefore to change the contents of the cell to a string representation of the date. This would mess up any formulas that were doing any calculations on those dates (such as number of days btweeen dates etc). If you want to go down this route then this macro will convert all selected cells into text with the desired formula:
Sub ConvertDates
Dim r as range
For each r in selection
r.formula = format(r.value,"dd.mm.yyyy")
next r
end sub
answered Nov 22 at 14:54
Harassed Dad
2,5661511
add a comment |
up vote
0
down vote
The date format dd.mm.yyyy is not recognised as a date by Excel. You would have therefore to change the contents of the cell to a string representation of the date. This would mess up any formulas that were doing any calculations on those dates (such as number of days btweeen dates etc). If you want to go down this route then this macro will convert all selected cells into text with the desired formula:
Sub ConvertDates
Dim r as range
For each r in selection
r.formula = format(r.value,"dd.mm.yyyy")
next r
end sub
answered Nov 22 at 14:54
Harassed Dad
2,5661511
add a comment |
up vote
0
down vote
up vote
0
down vote
The date format dd.mm.yyyy is not recognised as a date by Excel. You would have therefore to change the contents of the cell to a string representation of the date. This would mess up any formulas that were doing any calculations on those dates (such as number of days btweeen dates etc). If you want to go down this route then this macro will convert all selected cells into text with the desired formula:
Sub ConvertDates
Dim r as range
For each r in selection
r.formula = format(r.value,"dd.mm.yyyy")
next r
end sub
answered Nov 22 at 14:54
Harassed Dad
2,5661511
The date format dd.mm.yyyy is not recognised as a date by Excel. You would have therefore to change the contents of the cell to a string representation of the date. This would mess up any formulas that were doing any calculations on those dates (such as number of days btweeen dates etc). If you want to go down this route then this macro will convert all selected cells into text with the desired formula:
Sub ConvertDates
Dim r as range
For each r in selection
r.formula = format(r.value,"dd.mm.yyyy")
next r
end sub
answered Nov 22 at 14:54
Harassed Dad
2,5661511
answered Nov 22 at 14:54
Harassed Dad
2,5661511
answered Nov 22 at 14:54
Harassed Dad
2,5661511
answered Nov 22 at 14:54
Harassed Dad
2,5661511
2,5661511
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53425722%2fneed-to-have-date-format-as-dd-mm-yyyy-for-column-using-excel-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
