date/text formula which propagates on drag

Hello All,

I am using Excel 2003 (or Excel 2007 if needed) and have the following problem, which I can not find an answer to.

The template will start with 'Day 01' in cell A1. If I have been given the date, I will change cell A1 to the date, but want to the other cells in column A to change automatically based on the contents of cell A1.

In Cell 'A1' default is 'Day 01' or I may change it to actual date '31-02-13'

In Cell A2, I want to show either 'Day 02', or the date '01-03-13'.

If I enter =SUM(A1)+1, cell A2 will display as 01-01-00, digits (or some other format(. I have tried various formats but it doesn't work.

Secondly, I tried the following IF statement in Cell A2:
=IF(A1="Day 01","Day 02",(A1+1))

The problem is, when I drag that function into cell A3, the function is
=IF(A2="Day 01","Day 02",(A2+1)) - the two bits in quotes are not incremental.

Any ideas what kind of formula I can use to solve my little problem?

Thanks!
Andy

Hi YesMayBe,

31-02-13 will not work as Feb 31 will never come

Otherwise, it should work when you add +1 to the below formulas .. if you still face issue, upload a small sample workbook.. thanks,

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi YesMayBe,

See the two options in column F and I in attached workbook.. thanks.
date-if-sample.xls

Regards,
DILIPandey

<click on below 'star' if this helps>

Ok.. try below formula:-

Formula:

Please Login or Register  to view this content.

To test this, enter either the day ## or any date in yellow cell and watch the results in green cell in below attachment:-
date-if-sample.xls

Regards,
DILIPandey

<click on below 'star' if this helps>

Ok.. here is the explanation:-

Why right and Why 3?
VALUE(RIGHT(H15,3))
Right is a function which is extracting 3 characters from right and then Value function converts that into a value (removing extra spaces and converting the number from text format to number in number format


Hope this helps.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

You are welcome YesMayBe

cheeers

Regards,
DILIPandey


<click on below 'star' if this helps>