Extract numbers from date to number format

[Jonathan9]

I need to extract/separate numbers from a date cell - for instance:

24-09-13

but when I do this:

=RIGHT(B2,2)

I get the number 41 and 13. I presume this has something to do with date and number format. Haven't been able to fix it. Does anybody know a good trick?

Edit:

Because if I change the 24-09-13 to number instead of date it turns into a weird unusable sequence of numbers such as 41541.

[Richard Buttrey]

Hi,

What result are you expecting?

If your B2 cell is a proper date number, i.e. 41541 for 24th Sept 2013 then you will get the result 41.

If your B2 cell is the text '24-09-13 then you'll get 13.

It's nothing to do with formatting, rather what's actually in the B2 cell.

Has Berlin been over-run?

[TMS]

A date is a number counting from 01/01/1900 ... which is day 1. So you're just getting the right and left digits.

Use the functions DAY, MONTH and YEAR to get ... well, probably speaks for itself.


Regards, TMS

[Pete_UK]

If it is a proper date then you can use YEAR() or MONTH() or DAY() to extract those parts of it that you need.

If you are more comfortable using string functions, then you can do this:

=RIGHT(TEXT(B2,"dd-mm-yy"),2)

but it is just the same as doing this:

=YEAR(B2)

or if you only want the last two digits of the year:

=MOD(YEAR(B2),100)

Hope this helps.

Pete

[Jonathan9]

Hi,

What result are you expecting?

If your B2 cell is a proper date number, i.e. 41541 for 24th Sept 2013 then you will get the result 41.

If your B2 cell is the text '24-09-13 then you'll get 13.

It's nothing to do with formatting, rather what's actually in the B2 cell.

Has Berlin been over-run?

Well, I was expecting to return 13 but Excel had me fooled once again...

A date is a number counting from 01/01/1900 ... which is day 1. So you're just getting the right and left digits.

Use the functions DAY, MONTH and YEAR to get ... well, probably speaks for itself.


Regards, TMS

Yes!

If it is a proper date then you can use YEAR() or MONTH() or DAY() to extract those parts of it that you need.

If you are more comfortable using string functions, then you can do this:

=RIGHT(TEXT(B2,"dd-mm-yy"),2)

but it is just the same as doing this:

=YEAR(B2)

or if you only want the last two digits of the year:

=MOD(YEAR(B2),100)

Hope this helps.

Pete

Yes, this worked like charm. Appreciate it.

[Ghozi Alkatiri]

it depend on your regional setting number of your computer.
to ensure if data at B2 date value or not use isnumber function. if you get true, it means date value if false it means not.
so if you want to get 2 end number of year from data at B2 try this one.
=IF(ISNUMBER(B2),RIGHT(YEAR(B2),2),RIGHT(B2,2))

[TMS]

You're welcome.