text()

[lezawang]

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>

 

[Guru008]

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]

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]

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]

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]

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]

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]

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]

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")

 

[lezawang]

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.