Convert Calendar month to Financial Month

SamS · Mar 5th, 2002, 05:53 PM

I need a formula that looks at todays date and returns the financial month (not calendar)as an integer eg February should be returned as 8 and can be either +ve or -ve as I will use abs() to convert it. I know it can be done with a lookup table but I was hoping to avoid this. Should be easy but maths was not my strong subject.

Mar 5th, 2002, 06:05 PM

=MONTH(TODAY())

Mar 5th, 2002, 06:08 PM

Should read :-

=MONTH(TODAY())+6

SamS · Mar 5th, 2002, 06:24 PM

Quote:

On 2002-03-05 17:08, Anonymous wrote:
Should read :-

=MONTH(TODAY())+6

not quite, the financial periods run from July to June therefore the above formulas for December returns 18 where as it should return 6.

Mar 5th, 2002, 06:53 PM

=IF(MONTH(B1)>6,MONTH(B1)-6,MONTH(B1)+6)

SamS · Mar 5th, 2002, 07:54 PM

Thanks - modified to fit my requirement of current month as follows:
=IF(MONTH(NOW())>6,ABS(MONTH(NOW())-6),MONTH(NOW())+6)

should have known this

ops

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 5th, 2002, 05:53 PM	#1
SamS Board Regular Join Date: Feb 2002 Location: Brisbane, Down Under Posts: 533	I need a formula that looks at todays date and returns the financial month (not calendar)as an integer eg February should be returned as 8 and can be either +ve or -ve as I will use abs() to convert it. I know it can be done with a lookup table but I was hoping to avoid this. Should be easy but maths was not my strong subject.

Mar 5th, 2002, 06:08 PM	#3
Guest Posts: n/a	Should read :- =MONTH(TODAY())+6

Mar 5th, 2002, 06:53 PM	#5
Guest Posts: n/a	=IF(MONTH(B1)>6,MONTH(B1)-6,MONTH(B1)+6)

Mar 5th, 2002, 07:54 PM	#6
SamS Board Regular Join Date: Feb 2002 Location: Brisbane, Down Under Posts: 533	Thanks - modified to fit my requirement of current month as follows: =IF(MONTH(NOW())>6,ABS(MONTH(NOW())-6),MONTH(NOW())+6) should have known this ops