Calculating Excess Days Formula
 

Hello,

I am working on a database that shows the number of days charged for a
service within a given month:

Days Charged Excess Days Charged
Apr May Jun Jul Apr May Jun Jul
2 33 30 7 0 2 0 0

The following formula is used to calculate the number of excess days i.e.
when we have been charged for more days than are in the month (this is a
regular occurance):

=IF(A3-DAY(DATE(YEAR(A$2),MONTH(A$2)+1,1)-1)0,A3-DAY(DATE(YEAR(A$2),MONTH(A$2)+1,1)-1),0)

This works perfectly. However, there is a clause in the contract stating
that the provider is allowed to charge for this service in multiples of
3-days, i.e. if the service was used for 4-days then they can still charge us
for 6-days, following on they can charge us for more than the number of days
in a month i.e. 33 days in January.

I need to change the above formula to allow for this, so in the case of the
example above, the number of excess days for May will be 0. Is there anyway
that I can do this. I have spent a lot of time looking into this and its
driving me mad.

Thank you for your help. Cheers Richard

Calculating Excess Days Formula
 

=MAX(A3-CEILING(DAY(DATE(YEAR(A$2),MONTH(A$2)+1,0)),3),0)

On Jan 30, 3:29 pm, richardwo
wrote:
Hello,

I am working on a database that shows the number of days charged for a
service within a given month:

Days Charged Excess Days Charged
Apr May Jun Jul Apr May Jun Jul
2 33 30 7 0 2 0 0

The following formula is used to calculate the number of excess days i.e.
when we have been charged for more days than are in the month (this is a
regular occurance):

=IF(A3-DAY(DATE(YEAR(A$2),MONTH(A$2)+1,1)-1)0,A3-DAY(DATE(YEAR(A$2),MONTH(A$2)+1,1)-1),0)

This works perfectly. However, there is a clause in the contract stating
that the provider is allowed to charge for this service in multiples of
3-days, i.e. if the service was used for 4-days then they can still charge us
for 6-days, following on they can charge us for more than the number of days
in a month i.e. 33 days in January.

I need to change the above formula to allow for this, so in the case of the
example above, the number of excess days for May will be 0. Is there anyway
that I can do this. I have spent a lot of time looking into this and its
driving me mad.

Thank you for your help. Cheers Richard

Calculating Excess Days Formula
 

Try something like this:

=MAX(A3-CEILING(DAY(DATE(YEAR(A$2),MONTH(A$2)+1,0)),3),0)


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"richardwo" wrote:

Hello,

I am working on a database that shows the number of days charged for a
service within a given month:

Days Charged Excess Days Charged
Apr May Jun Jul Apr May Jun Jul
2 33 30 7 0 2 0 0

The following formula is used to calculate the number of excess days i.e.
when we have been charged for more days than are in the month (this is a
regular occurance):

=IF(A3-DAY(DATE(YEAR(A$2),MONTH(A$2)+1,1)-1)0,A3-DAY(DATE(YEAR(A$2),MONTH(A$2)+1,1)-1),0)

This works perfectly. However, there is a clause in the contract stating
that the provider is allowed to charge for this service in multiples of
3-days, i.e. if the service was used for 4-days then they can still charge us
for 6-days, following on they can charge us for more than the number of days
in a month i.e. 33 days in January.

I need to change the above formula to allow for this, so in the case of the
example above, the number of excess days for May will be 0. Is there anyway
that I can do this. I have spent a lot of time looking into this and its
driving me mad.

Thank you for your help. Cheers Richard

Calculating Excess Days Formula
 

Thank you so much Ron. Works perfectly. Cheers

"Ron Coderre" wrote:

Try something like this:

=MAX(A3-CEILING(DAY(DATE(YEAR(A$2),MONTH(A$2)+1,0)),3),0)


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"richardwo" wrote:

Hello,

I am working on a database that shows the number of days charged for a
service within a given month:

Days Charged Excess Days Charged
Apr May Jun Jul Apr May Jun Jul
2 33 30 7 0 2 0 0

The following formula is used to calculate the number of excess days i.e.
when we have been charged for more days than are in the month (this is a
regular occurance):

=IF(A3-DAY(DATE(YEAR(A$2),MONTH(A$2)+1,1)-1)0,A3-DAY(DATE(YEAR(A$2),MONTH(A$2)+1,1)-1),0)

This works perfectly. However, there is a clause in the contract stating
that the provider is allowed to charge for this service in multiples of
3-days, i.e. if the service was used for 4-days then they can still charge us
for 6-days, following on they can charge us for more than the number of days
in a month i.e. 33 days in January.

I need to change the above formula to allow for this, so in the case of the
example above, the number of excess days for May will be 0. Is there anyway
that I can do this. I have spent a lot of time looking into this and its
driving me mad.

Thank you for your help. Cheers Richard

Calculating Excess Days Formula
 

Thanks for the feedback!....I'm glad that worked for you.


***********
Regards,
Ron

XL2002, WinXP


"richardwo" wrote:

Thank you so much Ron. Works perfectly. Cheers

"Ron Coderre" wrote:

Try something like this:

=MAX(A3-CEILING(DAY(DATE(YEAR(A$2),MONTH(A$2)+1,0)),3),0)


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"richardwo" wrote:

Hello,

I am working on a database that shows the number of days charged for a
service within a given month:

Days Charged Excess Days Charged
Apr May Jun Jul Apr May Jun Jul
2 33 30 7 0 2 0 0

The following formula is used to calculate the number of excess days i.e.
when we have been charged for more days than are in the month (this is a
regular occurance):

=IF(A3-DAY(DATE(YEAR(A$2),MONTH(A$2)+1,1)-1)0,A3-DAY(DATE(YEAR(A$2),MONTH(A$2)+1,1)-1),0)

This works perfectly. However, there is a clause in the contract stating
that the provider is allowed to charge for this service in multiples of
3-days, i.e. if the service was used for 4-days then they can still charge us
for 6-days, following on they can charge us for more than the number of days
in a month i.e. 33 days in January.

I need to change the above formula to allow for this, so in the case of the
example above, the number of excess days for May will be 0. Is there anyway
that I can do this. I have spent a lot of time looking into this and its
driving me mad.

Thank you for your help. Cheers Richard