Hi Khanaran,
When I first saw this I thought it was going to be a simple matter of change of format for the date display, but when I saw your sheet I realised that some juggling was required to split up the digits of a date into individual cells. You were on the right lines in cell I2 because you are obviously aware of the DAY(), MONTH() and YEAR() functions in Excel to extract just that portion of a date as a number, importantly not in date format. I can also see from that formula that you can use the MOD function too.
The answer lies in a combination of those functions and the INT()function. You probably know that the INT() function just keeps the whole number part of a division sum and ignores whats after the decimal point.The MOD() function does the opposite, keeping only the bit after the decimal point as an integer.
I think if you look at my formulas you'll figure out what i did.
I found the day and month digits the easiest to split up using the INT() and MOD() functions. The year was more difficult because of the four digits, so I had to combine both INT() and MOD() in the same formula in some cases, but I got there in the end.
For the other problem, I'm assuming theres a typo error when you referred to cell N4, I think you meant N2.
This is a classic case of using the VLOOKUP() function to look up in a table a list of values which are on the same row as another number. So in the case of number 4 (in cell N2) we need to look up what other values are on the row labelled 4 in your table. Firstly I gave the range W3:AB11 (your table) a name. I called it reftable. The easiest way to name a table is to highlight the area, go to the NAME box to the left of the formula bar and type in the name, then press <enter>.
Firstly, I made cell value N2 equal to cell value I2 as this is our lookup value.
In cell O2 I have the formula:- =VLOOKUP($N2,reftable,2)
This means find whatever value is in cell N2 in the first column of reftable, then return the value in the second column to cell O2.
The other formulas in cells O2 to S2 return the other values in columns 3,4,5,6 on that same row.
Just for fun I used conditional formatting to auto highlight the yellow row of the table.
I think that wraps up what you asked for, I hope you can figure out what I did.
http://www.mediafire.com/file/y7frsljdyxyekyy/datenumero2.xlsx/file
Good luck,
Frank
