[mso] Re: Date functions in Excel

 Jens,

It appears that you are in the UK, but may receive dates in US format as
well as UK format.

The good news is that a date is a number, whatever country you are in,
consisting of the number of days (and fractions of a day) since midnight on
1 Jan 1900 - in the case of Excel for Windows.  It matters not how the dates
are displayed on screen - '31/3/03', '31-Mar-03', '03/31/03' all mean the
same and if you were to format the cell as Number, you would see 37711.00,
which is the number of days elapsed since 'base date'.

If you are looking to find which financial year a date falls in - especially
if you are dealing with more than one financial year - then you should
consider Lookup Tables - VLOOKUP (or HLOOKUP).

In a separate table you will have the boundaries of the financial years
with, next to them, the [text] you require to be displayed:

31/03/02        |2002/3
31/03/03        |2003/4
...

It helps (but isn't essential) to select this table range and to name it as,
for example, Fyear_lookup (check out Naming Ranges, if you're not sure)

In the cell you want to display the financial year, use
VLOOKUP([Value_to_lookup],[Range_of_Table],[Column_of_Table_to_display])

So if your date is in A1 and you want to show the financial year in B1, the
function in B1 would be something like =VLOOKUP(A1,Fyear_lookup,2).

Note that Column 1 of the table always contains the data you need to look up
(in this case the dates), Column 2 (and so forth) contains the value you
want to be displayed as the result of your lookup.

HTH,

Andrew

> -----Original Message-----
> From: mso-bounce@xxxxxxxxxxxxx 
> [mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf Of Jens Outzen
> Sent: 18 April 2005 12:20
> To: mso@xxxxxxxxxxxxx
> Subject: [mso] Date functions in Excel
> 
> 
> 
> 
> 
> 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:
> 
> =IF(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: