On Monday, August 11, 2014 12:08:24 PM UTC+5:30, San wrote:
On Monday, August 11, 2014 11:49:10 AM UTC+5:30, Claus Busch wrote:

Hi,







Am Sun, 10 Aug 2014 18:45:10 -0700 (PDT) schrieb San:







BTW if I need to trap the expenses week-wise say for the current week, Week[-1] and Week[-2] I can use the WEEKNUM(Today(),2), WEEKNUM(Today(),2)-1 and WEEKNUM(Today(),2)-2 for getting the same. But again I face the same problem when the Current week is the 1st week of January.







try:



=WEEKNUM(TODAY(),2)



=WEEKNUM(TODAY()-7,2)



=WEEKNUM(TODAY()-14,2)















Regards



Claus B.



--



Vista Ultimate / Windows7



Office 2007 Ultimate / 2010 Professional



Great!.. I should have thought in that line. Thanks

If I have to sum on a Financial Year-wise basis (i.e. from April - Mar of next year), I have inserted a column where the Financial Year is calculated as follows:

E2=IF(OR(C2=1,C2=2,C2=3),YEAR(A2)-1,YEAR(A2)) [where C2 = Month serial no.of A2)


Now for Financial Year-wise tabulation, say for the last Financial Year, the formula in Cell T31 is

T31= IF(OR(MONTH(TODAY())=1,MONTH(TODAY())=2,MONTH(TODA Y())=3),ROUND(SUM(INDEX(EMV5!$AA$2:$AA$3615,MATCH( YEAR(EDATE(TODAY(),-12))-1,EMV5!$E$2:$E$3615,0)):EMV5!$AA$3615),0)-SUM(T$30:T30),ROUND(SUM(INDEX(EMV5!$AA$2:$AA$3615, MATCH(YEAR(EDATE(TODAY(),-12)),EMV5!$E$2:$E$3615,0)):EMV5!$AA$3615),0))-SUM(T$30:T30)... where Cell T30 gives the summated value for the current Financial Year

Any simpler formula for the above?

Thanks

San