Difficulties with date in conditional calculations
 

Hi all

I am currently trying to generate a table to add up all payments due to
suppliers per month, using the function "DSUM('PAYMENT
DUE'!$B$4:$I$1073,"PAYMENT DUE",A36:C38)", with 'A36:C38' in this example
containing the criteria determining the month in which the payment is due,
e.g. '=01-Jan' and '<=31-Jan'.

However, the year of the dates in the criteria is automatically populated as
2008. Therefore, payments due in any other year is not added in the
calculation. This is problematic since the seasons determining the payments
usually fall in more than one year.

How can I bypass the year-issue, i.e. so that either the year value is
ignored in the calculations, or that the criteria can be adapted to include
all year values?

Regards
B

Difficulties with date in conditional calculations
 

Basha,

The easiest thing to do would be to add a column to your database, with the formula

=MONTH(cell with date)

and then do your DSUM based on that value being =1

HTH,
Bernie
MS Excel MVP


"Basha" wrote in message
...
Hi all

I am currently trying to generate a table to add up all payments due to
suppliers per month, using the function "DSUM('PAYMENT
DUE'!$B$4:$I$1073,"PAYMENT DUE",A36:C38)", with 'A36:C38' in this example
containing the criteria determining the month in which the payment is due,
e.g. '=01-Jan' and '<=31-Jan'.

However, the year of the dates in the criteria is automatically populated as
2008. Therefore, payments due in any other year is not added in the
calculation. This is problematic since the seasons determining the payments
usually fall in more than one year.

How can I bypass the year-issue, i.e. so that either the year value is
ignored in the calculations, or that the criteria can be adapted to include
all year values?

Regards
B

Difficulties with date in conditional calculations
 

Thanks Bernie, this is working perfectly!

B

"Bernie Deitrick" wrote:

Basha,

The easiest thing to do would be to add a column to your database, with the formula

=MONTH(cell with date)

and then do your DSUM based on that value being =1

HTH,
Bernie
MS Excel MVP


"Basha" wrote in message
...
Hi all

I am currently trying to generate a table to add up all payments due to
suppliers per month, using the function "DSUM('PAYMENT
DUE'!$B$4:$I$1073,"PAYMENT DUE",A36:C38)", with 'A36:C38' in this example
containing the criteria determining the month in which the payment is due,
e.g. '=01-Jan' and '<=31-Jan'.

However, the year of the dates in the criteria is automatically populated as
2008. Therefore, payments due in any other year is not added in the
calculation. This is problematic since the seasons determining the payments
usually fall in more than one year.

How can I bypass the year-issue, i.e. so that either the year value is
ignored in the calculations, or that the criteria can be adapted to include
all year values?

Regards
B