# Thread: Date math (Excel 2003)

1. ## Date math (Excel 2003)

In <post#=406650>post 406650</post#> I got a way to get a fiscal quarter from a date. I am now trying to simply get the Fiscal year end date so that any date from 4/1/05 to 3/31/06 would = 3/31/06 and 4/1/06-3/31/07 = 3/31/07, or 2006 and 2007 would be fine. Can't seem to get date math down. I'll keep looking but any help would be appreciated. TYIA!

2. ## Re: Date math (Excel 2003)

Assuming your date is in A1:

=IF(A1>DATE(YEAR(A1),3,31),DATE(IF(MONTH(A1)<=3,YE AR(A1),YEAR(A1)+1),3,31),DATE(YEAR(A1),3,31))

3. ## Re: Date math (Excel 2003)

Or slightly simpler
<code>
=DATE(YEAR(A1)+(MONTH(A1)>3),3,31)
</code>
This uses the fact that FALSE = 0 and TRUE = 1. If MONTH(A1) is greater than 3 (i.e. April through December), MONTH(A1)>3 evaluates to TRUE=1, and this is added to the year to obtain a date in the next year.

4. ## Re: Date math (Excel 2003)

Thanks! Both work well and I'll add them to my library. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•