is it possible to change dates 1 to 30 march to 1 to 30 April at once using find and replace ?

[learning_grexcel]

I prepare some reports every month in excel and usually it contains some 30 sheets. One of the columns is usually date in every sheet. The problem is every month on 1st, I have to go to manually every sheet and change the date from 1st March to 1st April in one cell, then drag till 30th. It takes lots of time to change in all sheets.

Is there anyway I can just replace 1 to 30th March to 1 to 30th April altogether.........

 

[Marcelo Branco]

Hi,

Maybe this

Put the current Month and Year in two fixed cells in, say Sheet1,
Month in AA1=4
Year in AB1=2011

and use this formula in the column you have the dates in all sheets
=DATE(Sheet1!$AB$1,Sheet1!$AA$1,ROWS($1:1))
copy down

Next month you change the value in AA1 from 4 (April) to 5 (May)

Next year you change AB1 from 2011 to 2012...

HTH

M.

 

[learning_grexcel]

Wow it's great. Thanks a lot

Is there anyway I can get the date in this format (1-Apr-11)

 

[Marcelo Branco]

You are welcome.

About your question: you can format the dates accordingly (dd/mmm/yyyy or dd/mmm/yy). You have to do it only once.

HTH

M.

 

[learning_grexcel]

Thanks dude, it works good, the only thing I would like to know is what here that "ROWS(1$:1)" means.......
I would like to know that so that I can use that in some other functions

 

[Marcelo Branco]

Hi,

The function ROWS(aRange) counts the number of rows of aRange

ROWS($1:1) counts the number of rows of the range $1:1, ie the row 1, and produces 1 as result

When you copy down it becomes ROWS($1:2) because of the absolute reference (the $) in the first part and the relative reference in the second part (no $). So it counts the number of rows of the range $1:2, ie rows 1 and 2, and produces 2 as result

When you copy down one more time it becomes Rows($1:3)...

and so on.

Hope i made myself clear.

All the best

M.

 

[learning_grexcel]

it's awesome. Thank you.

 

[Peter_SSs]

You could use an even simpler setup/formula for the original problem.

1. Put the first whole date (eg 1 March 2011) in the appropriate cell in the first sheet. Lets say this is cell A2.

2. On that first sheet in A3 put the formula
=A2+1
and drag down**

3. On each of the other sheets in A2 put a formula like
='first sheet name'!A2
and drag down.

Next month you simply have to change that date on the first sheet cell A2 to
1 April 2011


** If you want your dates to go right to the end of each month (28/29 for February, 31 for March, 30 for April etc), change the formula I gave for A2 in that first sheet to
=IF(LOOKUP(9.99999999999999E+307,A$2:A2)=EOMONTH(A$2,0),"",A2+1)
and ensure it is copied down far enough for a month with 31 days.
Note that this needs to be done on the first sheet only.

 

[learning_grexcel]

Hi,

The function ROWS(aRange) counts the number of rows of aRange

ROWS($1:1) counts the number of rows of the range $1:1, ie the row 1, and produces 1 as result

When you copy down it becomes ROWS($1:2) because of the absolute reference (the $) in the first part and the relative reference in the second part (no $). So it counts the number of rows of the range $1:2, ie rows 1 and 2, and produces 2 as result

When you copy down one more time it becomes Rows($1:3)...

and so on.

Hope i made myself clear.

All the best

M.

Thanks Peter_SSs and Marcelo again.
@Marcelo
I have been using formula in some excel sheets and in some Peter_SSs. I still cannot get what is the purpose of using ROW in your DATE Formula. Could you please make it clear to me?
I know you already explained why it's used in General but I cannot understand why it is used in your formula.

 

[Marcelo Branco]

Thanks Peter_SSs and Marcelo again.
@Marcelo
I have been using formula in some excel sheets and in some Peter_SSs. I still cannot get what is the purpose of using ROW in your DATE Formula. Could you please make it clear to me?
I know you already explained why it's used in General but I cannot understand why it is used in your formula.

Hi learning_grexcel,

The only purpose of ROWS($1,1) in the formula below is to generate consecutive numbers, beginning in 1, when the formula is copied down. These consecutive numbers are used as the day-parameter of the function DATE.

=DATE(Sheet1!$AB$1,Sheet1!$AA$1,ROWS($1:1))

As you know the function DATE has 3 parameters as Date(year, month, day) and in this specific formula the year and the month are fixed (absolute references to cells Sheet1!$AB$1 and Sheet1!$AA$1).

That said, notice that in the the first row
ROWS($1:1) = 1 (day 1 of the month)
copying down it becomes
2
3
4
...

producing the consecutive days of that month-year

HTH

M.