Changing a date to the visible formatted number

[parkerbelt]

I have a cell with =Now() in it that is formatted like yyyy - so only the year is visible - and then, in another cell, I subtract 365 from the cell to get the number for last year - it is formatted the same - yyyy.

I want the 2nd cell or another one, if necessary, to only contain the 4 digit year, like this 2016, rather than the number that represents the year - 42656.49. When I copy and paste special values, I get the 42656.49.

Is there a way to get the final cell to just contain the 2016 and not have it have a hidden number that the 2016 represents?

I want to be able to concatenate the 2016 with 01, so I can look for 201601 in a column of numbers.

 

[Joe4]

Formatting just changes how it is presented to you. The long number is actually how Excel sees and stores it (literally, it is the number of days since 1/0/1900).
So no matter how you format it, Excel will see it as that number.

If you want the cell to physically hold the year and not the number, you will need to convert by doing one of the following:

- Use the YEAR function to just return the year, i.e.

=YEAR(NOW())

this returns the number 2017

- Convert it to text using the TEXT function, i.e.

=TEXT(NOW(),"yyyy")

this returns the text value "2017"

 

[parkerbelt]

That Worked. Thank you!

But, now i'm having a problem getting the 01 to not copy over as 1 when I do the concatenate.
I have it stored in a cell formatted as custom - Type: 00

Is there any way to concatenate a cell with the 2016 in it and a cell with 01 in it to get 201601??

Formatting just changes how it is presented to you. The long number is actually how Excel sees and stores it (literally, it is the number of days since 1/0/1900).
So no matter how you format it, Excel will see it as that number.

If you want the cell to physically hold the year and not the number, you will need to convert by doing one of the following:

- Use the YEAR function to just return the year, i.e.

=YEAR(NOW())

this returns the number 2017

- Convert it to text using the TEXT function, i.e.

=TEXT(NOW(),"yyyy")

this returns the text value "2017"

 

[Joe4]

If you use the TEXT function, which returns a TEXT value (and not a number), it will maintain leading zeroes.

Using the TEXT function is pretty easy if you are used to doing custom formatting. Just enter the formatting value in the second argument.

So, if the 1 is from a number, you would just use:

=TEXT(A1,"00")

If the one is coming from the month of a date, just use:

=TEXT(A1,"mm")

If you have some date and want to return the year and month, like "201601", just use:

=TEXT(A1,"yyyymm")

 

[parkerbelt]

That worked as well. Thank you!

If you use the TEXT function, which returns a TEXT value (and not a number), it will maintain leading zeroes.

Using the TEXT function is pretty easy if you are used to doing custom formatting. Just enter the formatting value in the second argument.

So, if the 1 is from a number, you would just use:

=TEXT(A1,"00")

If the one is coming from the month of a date, just use:

=TEXT(A1,"mm")

If you have some date and want to return the year and month, like "201601", just use:

=TEXT(A1,"yyyymm")