text()

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,651
Office Version
  1. 365
Platform
  1. Windows
Hello

I tried this function, text(10,"dd/mm") and the answer was 10/01 which is Jan 01? Does that mean excel default month to Jan in this case?

Thanks


<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Guru008

Board Regular
Joined
Dec 19, 2016
Messages
126
Hi,

No Jan is not default month in this case, if you change value from 10 to 1 it will give you different month
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
10 is the 10th day from January 1st 1900 so you get 10/01 as you have asked for the day and month with "dd/mm".
Changing it to 1 won't give you a different month, it will give you 01/01 and if you format it as "dd/mm/yyyy" it will give you 01/01/1900.
Changing it to 100 will give you 09/04/1900 formatted as "dd/mm/yyyy".
 

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,651
Office Version
  1. 365
Platform
  1. Windows
Thanks for the help. Yes changing the number to one the result will be 01/01. But here is another question, which year? when i click on that cell I got =text(1,"dd/mm") so I decided to copy that cell then "Special paste" the value only on anther cell. When I finished that and doubled click on the new cell i got
01-01-2017 !!

So here is my question how can I know the yea by just clicking on the cell which has the function =text(1,"dd/mm")


10 is the 10th day from January 1st 1900 so you get 10/01 as you have asked for the day and month with "dd/mm".
Changing it to 1 won't give you a different month, it will give you 01/01 and if you format it as "dd/mm/yyyy" it will give you 01/01/1900.
Changing it to 100 will give you 09/04/1900 formatted as "dd/mm/yyyy".
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723

ADVERTISEMENT

Look at it this way - you are taking one value and trying to change/convert it into 2 values.

As far as the year is concerned, if you don't tell excel what year to use, it will default to the current year. type in 1/1 and you will get 1/1/2017
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
If you are using it as the text function then it is the number of days since 1/1/1900 as text not a number.

If you type 1/1 in a cell directly then Excel will treat it as a real date (i.e. a number) in the current year.

When you paste the result of the text function as value it is still text but then when you double click the cell Excel will convert it to a real date/number and interpret it the same as if you typed it manually and so will default to the current year.
 

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,651
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thank you. Now I have this function on a cell A1 =TEXT(1,"dd/mm")
after press enter I will get 01/01 in A1, now how can I see the year?


If you are using it as the text function then it is the number of days since 1/1/1900 as text not a number.

If you type 1/1 in a cell directly then Excel will treat it as a real date (i.e. a number) in the current year.

When you paste the result of the text function as value it is still text but then when you double click the cell Excel will convert it to a real date/number and interpret it the same as if you typed it manually and so will default to the current year.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
If it is still the formula then it doesn't have a year. It is not a date/number, it is TEXT only.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,718
Office Version
  1. 2010
Platform
  1. Windows
Thank you. Now I have this function on a cell A1 =TEXT(1,"dd/mm")
after press enter I will get 01/01 in A1, now how can I see the year?
If you want to see the year, ask the TEXT function to show it to you...

=TEXT(1,"dd/mm/yyyy")
 
Last edited:

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,651
Office Version
  1. 365
Platform
  1. Windows
Oh my god, never thought about that. Thank you so so much.

If it is still the formula then it doesn't have a year. It is not a date/number, it is TEXT only.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,400
Messages
5,601,467
Members
414,452
Latest member
Dannysamworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top