formula getting first two numbers in another cell

Hi all,

I need help in finding the right formula getting only the first two numbers of a date.
Example1:
Cell a1
01-Jan-10

Cell a2
01

Example2
Cell a1
23-Mar-10

Cell a2
23

I need a formula in cell a2 that only gets first two numbers of another cell.

thank you,

Carlo

If you need the leading zero you probably have to format the formula cell as "Custom" setting format
to DD

TEXT(A1,"dd") with a date in A1 06/01/2010 this will display the correct result 06 but if you try turning it back into a number you will lose the leading zero if thats what you need

Is what you're looking for an the cells between B8 and B91 to be a 1 also or are you looking for it to increment as the days count, for example B9 B10 B11 should they be 2 3 4 or do you want them all to be 1?

I would also put this formula in your first monthly payment date EOMONTH(B4,0)+1 but you may need ro install the analysis tool pack if your using 2003 then it will automatically calculate as soon as you put a date

IFERROR(IF($A8<($B$4+($D$4*30)),"",IF(AND(SUM($B$7:B7)<CEILING($E$4*$C$4,1),$A$8<>"",DAY(A8)=DAY($B$4)),$E$4,"")),"")

replace your 1 with the day function entered as above

i've altered the date formula for you as well, even if you have a loan taken out in december the function will mark the correct payment day and date in january

In hindsight forget the formula to generate the dates automatically, you'd run into problems if someon took out a loan on 31/08/2010 or same date in january

Carlo, i'm glad we got there in the end. But, the issue i mentioned will cause you problems if someone takes a loan on the last day in any month that has 31 days and the following month has less. In that scenario it will miss the month that doesnt have 31 as the last day and every month in your range that doesnt go all the way to 31 I'm not familiar with the differences between Excel for mac 2008 and Excel for Windows 2007, but in the above scenario perhaps an if statement combined with the end of month function to state that if the day of your loan date = 31 or the month of your loan date = 2 then use end of month, otherwise the formula your already using.

If loans cant be taken out that late in the month then theres no issue