I need a formula which will return the number of days in the given month i.e January = 31, April = 30. Can this be achieved with reference to" Today()". The following conditional format formula, which I currently use, assumes that there are always 31 days in every month which obviously produces inaccurate results for many months of the year:

=IF(M4TODAY(),0,IF(N4=0,IF(((TODAY()-B4)<31),(TODAY()-B4)*(G4*0.0328767%),(G4*0.0328767%)*31),0))

I would like to replace "31" in the above with a formula that returns the correct number of days for the given month.

Hi,

Am Wed, 4 Feb 2015 18:27:41 +0000 schrieb Quilp:

I need a formula which will return the number of days in the given month
i.e January = 31, April = 30. Can this be achieved with reference to"
Today()". The following conditional format formula, which I currently
use, assumes that there are always 31 days in every month which
obviously produces inaccurate results for many months of the year:

the days of todays month is:
=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Wed, 4 Feb 2015 20:09:55 +0100 schrieb Claus Busch:

=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))

or:
=DAY(EOMONTH(TODAY(),0))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks, Claus, you always seem to be the guy who solves my problems! Could I trouble you to enter your suggested formula into the long conditional formula which I included in my original posting - I am unsure I understand what it is that I need to enter?

Hi,

Am Thu, 5 Feb 2015 14:13:55 +0000 schrieb Quilp:

Could I trouble you to enter your suggested formula into the long
conditional formula which I included in my original posting - I am
unsure I understand what it is that I need to enter?

try:

=IF(AND(TODAY()-B4<DAY(EOMONTH(TODAY(),0)),N4=0),(TODAY()-B4)*G4*0.0328767%,G4*0.0328767%*DAY(EOMONTH(TODAY( ),0)))*(M4<=TODAY())


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks Claus. For some reason, which I do not understand, your suggested formula returns an incorrect value. For the row in question, "0" should have been returned in L4, because N4 contains a monetary amount, but now it shows £9.21.
Taking row 7, L7 now shows £1.01 whereas it should show £1.12.


Bridging Loan 4 02/06/2014 28/02/2015 249 £0.00 £110.22 £110.22 £6.56 £0.55 Yes £0.04 £1.12 0
Bridging Loan 4 02/06/2014 28/02/2015 249 £0.00 £110.22 £110.22 £6.56 £0.55 Yes £0.04 £1.01 0

Any suggestions?

Regards
Quilp

Hi,

Am Fri, 6 Feb 2015 09:29:23 +0000 schrieb Quilp:

Bridging Loan
4 02/06/2014 28/02/2015 249 £0.00 £110.22 £110.22 £6.56 £0.55 Yes £0.04 £1.12 0
Bridging Loan
4 02/06/2014 28/02/2015 249 £0.00 £110.22 £110.22 £6.56 £0.55 Yes £0.04 £1.01 0

then try:
=IF(N4=0,IF(TODAY()-B4<DAY(EOMONTH(TODAY(),0)),(TODAY()-B4)*G4*0.0328767%,G4*0.0328767%*DAY(EOMONTH(TODAY( ),0))),0)*(M4<=TODAY())

If the result is not correct, please send me a file with data and the
expected output
claus_busch(at)t-online.de


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[quote=Claus Busch;1620031]Hi,

Am Fri, 6 Feb 2015 09:29:23 +0000 schrieb Quilp:

Bridging Loan
4 02/06/2014 28/02/2015 249 £0.00 £110.22 £110.22 £6.56 £0.55 Yes £0.04 £1.12 0
Bridging Loan
4 02/06/2014 28/02/2015 249 £0.00 £110.22 £110.22 £6.56 £0.55 Yes £0.04 £1.01 0

then try:
=IF(N4=0,IF(TODAY()-B4<DAY(EOMONTH(TODAY(),0)),(TODAY()-B4)*G4*0.0328767%,G4*0.0328767%*DAY(EOMONTH(TODAY( ),0))),0)*(M4<=TODAY())

If the result is not correct, please send me a file with data and the
expected output
claus_busch(at)t-online.de


My last post was partially incorrect. Although row B did not return the expected "0", row 7 was correct in returning £1.01 for February's 28 days instead of 31 as returned by my original formula.

[quote=Quilp;1620035]Later: Thanks Claus - your last formula works. Your patience and assistance is greatly appreciated - I would not have solved the problem on my own.