[mso] Re: Date functions in Excel

Try this.  The 2nd formula will give you the financial or fiscal year.
I included the first in case you want to calculate the quarter also.

1. Add a new column to the source data and enter the formula below to
calculate the quarter number for the fiscal year:
=3D"Q"&(MOD(CEILING(22+MONTH(F2)-9-1,3)/3,4)+1)

2. Add a new column to the source data and insert the formula below to
calculate the fiscal year number:
=3DIF(MONTH(F2)<=3D9,YEAR(F2),YEAR(F2)+1)

Note:=20
The number 9 in the formulas is the fiscal year month end (September).
Since it appears your financial or fiscal year ends in March, you should
replace the 9 with 3.

(Note: this formula came from the www.exceltip.com



Christine McDonald, CPA
Technical Specialist
Western Regional Office
Information Technology Division (RSA-4)

From: Jens Outzen [mailto:Jens.Outzen@xxxxxxxxxxxxxx]=20

Unfortunately not something that I normally work with - dates that is
:-)

I need to ascertain where a certain date falls - in relation to the
financial year.

My input is into a formatted cell (date formatted) from which I want a
function to ascertain which financial year it falls into.

I have tried something along the lines of this formula:

=3DIF(A1>31/03/03<01/04/04,"2003/04",0)

which generates 0 in ALL cases.

Once I get this to work (I have a sneaking suspicion that the problem is
between reading US date and UK date) I need to be able to expand this to
more than just the one year which I intend to do using nested IF's -
unless someone has a better solution.

Thanks for your assistance in advance.


Jens N. Outzen
Senior Commercial Analyst
Firstgroup
Paddington Station
0207 298 7300 ext 243


*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: