Thread: Date Formula Copy

Hi All,

I need to make up a separate date recording for each month of the year. Currently I am having to change the Month and Day into every single cell.

I am using the following formulas for various functions and sheets:

=COUNTIFS(A$3:A$519,"01-MAR-11",D$3:D$519,AZ$1,E$3:E$519,BC$1,K$3:K$519,BB$2)

=SUMIFS(L$3:L$519,A$3:A$519,"01-MAR-11",D$3:D$519,BN$1)

Is there a way of getting the date to update in the relevant boxes when I cut and paste this formula into the 30 or 31 day month columns, as as changing the date is very cumbersome to say the least not to mention time consuming.

Thanks in advance.

Not sure what exactly you mean... a sample would be nice...

if you want to replace the date in the formula, you can reference a cell containing the date. There are also functions to get first or last date of a month based on a date entered in the cell.. e.g. EOMONTH()...

but a sample sheet showing what exactly you need would be more helpful to us.

You can replace the "01-MARCH-11" in row4 with:

DATE(YEAR(TODAY()),MONTH(TODAY()),ROW()-3)

which is based on current month.... you can replace TODAY() with a cell reference containing any date within the month you want to report on.

In order to not return values from next month if the current month has less than 30 days...you can preclude the COUNTIFS with an IF

e.g.

in AR4:

=IF(MONTH(TODAY())<>MONTH(DATE(YEAR(TODAY()),MONTH(TODAY()),ROW()-3)),0,COUNTIFS(A$3:A$519,DATE(YEAR(TODAY()),MONTH(TODAY()),ROW()-3),D$3:D$519,AR$1,K$3:K$519,AR$2))