How to change the format of a cell without changing its content in Ms. Excel? [duplicate]
up vote
2
down vote
favorite
This question already has an answer here:
Changing Cell Date Formats to Text Format and Retain mm/dd/yy
3 answers
I want to change a number of cells formatted for date to text format, but unable to do so. For example, one cell contains 29/01/2018. When I changed the format to text, its changed to 43129. The question is : How to change the format, but retain the original content of the cell?
Any help would be appreciated
microsoft-excel microsoft-office
edited Nov 26 at 13:17
Dave M
12.7k92838
asked Nov 22 at 6:13
Agil GA
212
marked as duplicate by Máté Juhász, PeterH, bertieb, Dave M, music2myear Nov 26 at 21:08
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
up vote
2
down vote
favorite
This question already has an answer here:
Changing Cell Date Formats to Text Format and Retain mm/dd/yy
3 answers
I want to change a number of cells formatted for date to text format, but unable to do so. For example, one cell contains 29/01/2018. When I changed the format to text, its changed to 43129. The question is : How to change the format, but retain the original content of the cell?
Any help would be appreciated
microsoft-excel microsoft-office
edited Nov 26 at 13:17
Dave M
12.7k92838
asked Nov 22 at 6:13
Agil GA
212
marked as duplicate by Máté Juhász, PeterH, bertieb, Dave M, music2myear Nov 26 at 21:08
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
Why has this been downvoted?
– Dave
Nov 22 at 6:29
@DocBrown Wow, thats so much work for a seemingly small change. Well, thanks for the info anyway
– Agil GA
Nov 22 at 7:53
You can use TEXT function to translate the DATE to TEXT format.
– Lee
Nov 22 at 8:16
add a comment |
up vote
2
down vote
favorite
up vote
2
down vote
favorite
This question already has an answer here:
Changing Cell Date Formats to Text Format and Retain mm/dd/yy
3 answers
I want to change a number of cells formatted for date to text format, but unable to do so. For example, one cell contains 29/01/2018. When I changed the format to text, its changed to 43129. The question is : How to change the format, but retain the original content of the cell?
Any help would be appreciated
microsoft-excel microsoft-office
edited Nov 26 at 13:17
Dave M
12.7k92838
asked Nov 22 at 6:13
Agil GA
212
This question already has an answer here:
Changing Cell Date Formats to Text Format and Retain mm/dd/yy
3 answers
I want to change a number of cells formatted for date to text format, but unable to do so. For example, one cell contains 29/01/2018. When I changed the format to text, its changed to 43129. The question is : How to change the format, but retain the original content of the cell?
Any help would be appreciated
This question already has an answer here:
Changing Cell Date Formats to Text Format and Retain mm/dd/yy
3 answers
microsoft-excel microsoft-office
microsoft-excel microsoft-office
edited Nov 26 at 13:17
Dave M
12.7k92838
asked Nov 22 at 6:13
Agil GA
212
edited Nov 26 at 13:17
Dave M
12.7k92838
asked Nov 22 at 6:13
Agil GA
212
edited Nov 26 at 13:17
Dave M
12.7k92838
edited Nov 26 at 13:17
Dave M
12.7k92838
edited Nov 26 at 13:17
Dave M
12.7k92838
12.7k92838
asked Nov 22 at 6:13
Agil GA
212
asked Nov 22 at 6:13
Agil GA
212
asked Nov 22 at 6:13
Agil GA
212
212
marked as duplicate by Máté Juhász, PeterH, bertieb, Dave M, music2myear Nov 26 at 21:08
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Máté Juhász, PeterH, bertieb, Dave M, music2myear Nov 26 at 21:08
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
Why has this been downvoted?
– Dave
Nov 22 at 6:29
@DocBrown Wow, thats so much work for a seemingly small change. Well, thanks for the info anyway
– Agil GA
Nov 22 at 7:53
You can use TEXT function to translate the DATE to TEXT format.
– Lee
Nov 22 at 8:16
add a comment |
Why has this been downvoted?
– Dave
Nov 22 at 6:29
@DocBrown Wow, thats so much work for a seemingly small change. Well, thanks for the info anyway
– Agil GA
Nov 22 at 7:53
You can use TEXT function to translate the DATE to TEXT format.
– Lee
Nov 22 at 8:16
Why has this been downvoted?
– Dave
Nov 22 at 6:29
Why has this been downvoted?
– Dave
Nov 22 at 6:29
@DocBrown Wow, thats so much work for a seemingly small change. Well, thanks for the info anyway
– Agil GA
Nov 22 at 7:53
@DocBrown Wow, thats so much work for a seemingly small change. Well, thanks for the info anyway
– Agil GA
Nov 22 at 7:53
You can use TEXT function to translate the DATE to TEXT format.
– Lee
Nov 22 at 8:16
You can use TEXT function to translate the DATE to TEXT format.
– Lee
Nov 22 at 8:16
add a comment |
2 Answers
2
active
oldest
votes
up vote
1
down vote
Excel stores dates as numbers. 1 is 1st-Jan-1900.
You can use formatting to display a date any way you want to.
The date 29-Jan-2018 is the number 43129, so when you format the cell as "General" it will show that number.
You can select a different format, for example "Short Date" or "Long Date", which will show the date following the conventions of the regional settings of your computer.
Or you can format the cell with a custom format and use any format you like by specifying a format string. Some format string examples are
dd-mm-yyyy ' this will show as 29-01-2018
dd/mmm/yyyy ' this will show as 29/Jan/2018
Note that the format does NOT change the value of the cell, just how the cell is displayed.
Let me know if that helps or if you need more info.
answered Nov 22 at 7:13
teylyn
16.6k22438
thx, how do i input the custom format? Just a background story of my problem here, so i am using this online excel from my onedrive, and suddenly the date format changed to American. Its fucked up all the dates stored in the cells (we use dd/mm/yyyy, but the American format uses mm/dd/yyy). I tried to change it to my country's format, but whenever i put a date in any date-formatted cell it would swap the day and the month (e.g. if i typed 01/02/2018, it'll change to 02/01/2018). Thats why I asked this question, maybe its safer to store them in text format.
– Agil GA
Nov 22 at 8:09
No, don't store dates as text. That will even aggravate the situation. If you use Excel online, select the date cells and format them as you want them to display. If you want to remove ambiguity when entering dates, use 1-Feb-2019 or 2-Jan-2019 when you enter a date. That will make sure that the cell value will be the date you intend.
– teylyn
Nov 24 at 5:20
add a comment |
up vote
0
down vote
This method should work without using VBA. Lets say your dates are in column A.
add a helper column to the sheet. Lets say it is column B.
add the formular
=TEXT(A1,"dd/MM/yyyy")into cell B1, and fill the column downwards. (Adapt the format string if required.)format column C as text
use "copy values" to copy the content of column B to column C
Now you can delete column B (and if you like, column A as well).
answered Nov 22 at 12:11
Doc Brown
318312
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
Excel stores dates as numbers. 1 is 1st-Jan-1900.
You can use formatting to display a date any way you want to.
The date 29-Jan-2018 is the number 43129, so when you format the cell as "General" it will show that number.
You can select a different format, for example "Short Date" or "Long Date", which will show the date following the conventions of the regional settings of your computer.
Or you can format the cell with a custom format and use any format you like by specifying a format string. Some format string examples are
dd-mm-yyyy ' this will show as 29-01-2018
dd/mmm/yyyy ' this will show as 29/Jan/2018
Note that the format does NOT change the value of the cell, just how the cell is displayed.
Let me know if that helps or if you need more info.
answered Nov 22 at 7:13
teylyn
16.6k22438
thx, how do i input the custom format? Just a background story of my problem here, so i am using this online excel from my onedrive, and suddenly the date format changed to American. Its fucked up all the dates stored in the cells (we use dd/mm/yyyy, but the American format uses mm/dd/yyy). I tried to change it to my country's format, but whenever i put a date in any date-formatted cell it would swap the day and the month (e.g. if i typed 01/02/2018, it'll change to 02/01/2018). Thats why I asked this question, maybe its safer to store them in text format.
– Agil GA
Nov 22 at 8:09
No, don't store dates as text. That will even aggravate the situation. If you use Excel online, select the date cells and format them as you want them to display. If you want to remove ambiguity when entering dates, use 1-Feb-2019 or 2-Jan-2019 when you enter a date. That will make sure that the cell value will be the date you intend.
– teylyn
Nov 24 at 5:20
add a comment |
up vote
1
down vote
Excel stores dates as numbers. 1 is 1st-Jan-1900.
You can use formatting to display a date any way you want to.
The date 29-Jan-2018 is the number 43129, so when you format the cell as "General" it will show that number.
You can select a different format, for example "Short Date" or "Long Date", which will show the date following the conventions of the regional settings of your computer.
Or you can format the cell with a custom format and use any format you like by specifying a format string. Some format string examples are
dd-mm-yyyy ' this will show as 29-01-2018
dd/mmm/yyyy ' this will show as 29/Jan/2018
Note that the format does NOT change the value of the cell, just how the cell is displayed.
Let me know if that helps or if you need more info.
answered Nov 22 at 7:13
teylyn
16.6k22438
thx, how do i input the custom format? Just a background story of my problem here, so i am using this online excel from my onedrive, and suddenly the date format changed to American. Its fucked up all the dates stored in the cells (we use dd/mm/yyyy, but the American format uses mm/dd/yyy). I tried to change it to my country's format, but whenever i put a date in any date-formatted cell it would swap the day and the month (e.g. if i typed 01/02/2018, it'll change to 02/01/2018). Thats why I asked this question, maybe its safer to store them in text format.
– Agil GA
Nov 22 at 8:09
No, don't store dates as text. That will even aggravate the situation. If you use Excel online, select the date cells and format them as you want them to display. If you want to remove ambiguity when entering dates, use 1-Feb-2019 or 2-Jan-2019 when you enter a date. That will make sure that the cell value will be the date you intend.
– teylyn
Nov 24 at 5:20
add a comment |
up vote
1
down vote
up vote
1
down vote
Excel stores dates as numbers. 1 is 1st-Jan-1900.
You can use formatting to display a date any way you want to.
The date 29-Jan-2018 is the number 43129, so when you format the cell as "General" it will show that number.
You can select a different format, for example "Short Date" or "Long Date", which will show the date following the conventions of the regional settings of your computer.
Or you can format the cell with a custom format and use any format you like by specifying a format string. Some format string examples are
dd-mm-yyyy ' this will show as 29-01-2018
dd/mmm/yyyy ' this will show as 29/Jan/2018
Note that the format does NOT change the value of the cell, just how the cell is displayed.
Let me know if that helps or if you need more info.
answered Nov 22 at 7:13
teylyn
16.6k22438
Excel stores dates as numbers. 1 is 1st-Jan-1900.
You can use formatting to display a date any way you want to.
The date 29-Jan-2018 is the number 43129, so when you format the cell as "General" it will show that number.
You can select a different format, for example "Short Date" or "Long Date", which will show the date following the conventions of the regional settings of your computer.
Or you can format the cell with a custom format and use any format you like by specifying a format string. Some format string examples are
dd-mm-yyyy ' this will show as 29-01-2018
dd/mmm/yyyy ' this will show as 29/Jan/2018
Note that the format does NOT change the value of the cell, just how the cell is displayed.
Let me know if that helps or if you need more info.
answered Nov 22 at 7:13
teylyn
16.6k22438
answered Nov 22 at 7:13
teylyn
16.6k22438
answered Nov 22 at 7:13
teylyn
16.6k22438
answered Nov 22 at 7:13
teylyn
16.6k22438
16.6k22438
thx, how do i input the custom format? Just a background story of my problem here, so i am using this online excel from my onedrive, and suddenly the date format changed to American. Its fucked up all the dates stored in the cells (we use dd/mm/yyyy, but the American format uses mm/dd/yyy). I tried to change it to my country's format, but whenever i put a date in any date-formatted cell it would swap the day and the month (e.g. if i typed 01/02/2018, it'll change to 02/01/2018). Thats why I asked this question, maybe its safer to store them in text format.
– Agil GA
Nov 22 at 8:09
No, don't store dates as text. That will even aggravate the situation. If you use Excel online, select the date cells and format them as you want them to display. If you want to remove ambiguity when entering dates, use 1-Feb-2019 or 2-Jan-2019 when you enter a date. That will make sure that the cell value will be the date you intend.
– teylyn
Nov 24 at 5:20
add a comment |
thx, how do i input the custom format? Just a background story of my problem here, so i am using this online excel from my onedrive, and suddenly the date format changed to American. Its fucked up all the dates stored in the cells (we use dd/mm/yyyy, but the American format uses mm/dd/yyy). I tried to change it to my country's format, but whenever i put a date in any date-formatted cell it would swap the day and the month (e.g. if i typed 01/02/2018, it'll change to 02/01/2018). Thats why I asked this question, maybe its safer to store them in text format.
– Agil GA
Nov 22 at 8:09
No, don't store dates as text. That will even aggravate the situation. If you use Excel online, select the date cells and format them as you want them to display. If you want to remove ambiguity when entering dates, use 1-Feb-2019 or 2-Jan-2019 when you enter a date. That will make sure that the cell value will be the date you intend.
– teylyn
Nov 24 at 5:20
thx, how do i input the custom format? Just a background story of my problem here, so i am using this online excel from my onedrive, and suddenly the date format changed to American. Its fucked up all the dates stored in the cells (we use dd/mm/yyyy, but the American format uses mm/dd/yyy). I tried to change it to my country's format, but whenever i put a date in any date-formatted cell it would swap the day and the month (e.g. if i typed 01/02/2018, it'll change to 02/01/2018). Thats why I asked this question, maybe its safer to store them in text format.
– Agil GA
Nov 22 at 8:09
thx, how do i input the custom format? Just a background story of my problem here, so i am using this online excel from my onedrive, and suddenly the date format changed to American. Its fucked up all the dates stored in the cells (we use dd/mm/yyyy, but the American format uses mm/dd/yyy). I tried to change it to my country's format, but whenever i put a date in any date-formatted cell it would swap the day and the month (e.g. if i typed 01/02/2018, it'll change to 02/01/2018). Thats why I asked this question, maybe its safer to store them in text format.
– Agil GA
Nov 22 at 8:09
No, don't store dates as text. That will even aggravate the situation. If you use Excel online, select the date cells and format them as you want them to display. If you want to remove ambiguity when entering dates, use 1-Feb-2019 or 2-Jan-2019 when you enter a date. That will make sure that the cell value will be the date you intend.
– teylyn
Nov 24 at 5:20
No, don't store dates as text. That will even aggravate the situation. If you use Excel online, select the date cells and format them as you want them to display. If you want to remove ambiguity when entering dates, use 1-Feb-2019 or 2-Jan-2019 when you enter a date. That will make sure that the cell value will be the date you intend.
– teylyn
Nov 24 at 5:20
add a comment |
up vote
0
down vote
This method should work without using VBA. Lets say your dates are in column A.
add a helper column to the sheet. Lets say it is column B.
add the formular
=TEXT(A1,"dd/MM/yyyy")into cell B1, and fill the column downwards. (Adapt the format string if required.)format column C as text
use "copy values" to copy the content of column B to column C
Now you can delete column B (and if you like, column A as well).
answered Nov 22 at 12:11
Doc Brown
318312
add a comment |
up vote
0
down vote
This method should work without using VBA. Lets say your dates are in column A.
add a helper column to the sheet. Lets say it is column B.
add the formular
=TEXT(A1,"dd/MM/yyyy")into cell B1, and fill the column downwards. (Adapt the format string if required.)format column C as text
use "copy values" to copy the content of column B to column C
Now you can delete column B (and if you like, column A as well).
answered Nov 22 at 12:11
Doc Brown
318312
add a comment |
up vote
0
down vote
up vote
0
down vote
This method should work without using VBA. Lets say your dates are in column A.
add a helper column to the sheet. Lets say it is column B.
add the formular
=TEXT(A1,"dd/MM/yyyy")into cell B1, and fill the column downwards. (Adapt the format string if required.)format column C as text
use "copy values" to copy the content of column B to column C
Now you can delete column B (and if you like, column A as well).
answered Nov 22 at 12:11
Doc Brown
318312
This method should work without using VBA. Lets say your dates are in column A.
add a helper column to the sheet. Lets say it is column B.
add the formular
=TEXT(A1,"dd/MM/yyyy")into cell B1, and fill the column downwards. (Adapt the format string if required.)format column C as text
use "copy values" to copy the content of column B to column C
Now you can delete column B (and if you like, column A as well).
answered Nov 22 at 12:11
Doc Brown
318312
answered Nov 22 at 12:11
Doc Brown
318312
answered Nov 22 at 12:11
Doc Brown
318312
answered Nov 22 at 12:11
Doc Brown
318312
318312
add a comment |
add a comment |
Why has this been downvoted?
– Dave
Nov 22 at 6:29
@DocBrown Wow, thats so much work for a seemingly small change. Well, thanks for the info anyway
– Agil GA
Nov 22 at 7:53
You can use TEXT function to translate the DATE to TEXT format.
– Lee
Nov 22 at 8:16