U14 cannot minus itself? P14-O14-R14-S14-U14. If P14 is "", it will be less
than B7 < Data!B7, so this part does not make sense? What happens if O14 is
"", P14 = 12/03/2007 and Data!B7 =12/04/2007? You will always have a 0.
Does this mean that Data!B7 and Data!C7, R14, S14 are all dates? What
happens if you want to subtract 1/3/2007 from 15/2/2007?


--
Hth

Kassie Kasselman
Change xxx to hotmail


"mjones" wrote:

Hi All,

Four calculations are needed, but if I can figure out just this one, I
can get the other three. If possible, I'd like to avoid using a macro
because my client is the government and I'll have to deal with
security issues.

Cell Formula for $U14:
If $O14 and $P14 = blank - 0
If $O14 = blank and $P14 not blank - "Need start date"
If $P14 = blank and $O14 not blank - "Need end date"
If $P14 < Data!$B$7 - 0
If $O14 < Data!$B$7 and ($P14 Data!$B$7 and < Data!$C$7) - P14-O14-
R14-S14-U14

Data Validation for $O14 and $P14:
If $O14 < 1-Apr-07 or $P14 31-Mar-11 - "Dates must fall inside
2007-2010 fiscal years"
If $O14 $P14 - "Start date must be before end date"

Other stuff:
Date formats are dd-mmm-yy
All cells mentioned are dates.
Nice if errors conditional format to red, i.e.
=ISNUMBER(SEARCH("Need",A1))

Idea is to calculate how many days are worked in each of four fiscal
years based on:

Start date is $O14
End date is $P14

2007 # of days go in $R14
2008 # of days go in $S14
2009 # of days go in $T14
2010 # of days go in $U14 <- doing this one only right now

2007 fiscal period 1-Apr-07 to 31-Mar-08 - in Data!B4 and Data!C4
2008 fiscal period 1-Apr-08 to 31-Mar-09 - in Data!B5 and Data!C5
2009 fiscal period 1-Apr-09 to 31-Mar-10 - in Data!B6 and Data!C6
2010 fiscal period 1-Apr-10 to 31-Mar-11 - in Data!B7 and Data!C7 <-
only one relevant for now

After realizing that this is not simple, I spent three hours creating
a table with 16 different outcomes for each of the four different
year's "# of days" cells. I'm hoping that someone can help me get the
rest of the way and get these numbers to come out right.

Thank you for reading this,

Michele