Hi Kassie

Ah, sorry, typo. That should read T14 at the end as in:
If $O14 < Data!$B$7 and ($P14 Data!$B$7 and < Data!$C$7) - P14-O14-
R14-S14-T14

Does this mean that Data!B7 and Data!C7, R14, S14 are all dates? -
Yes, all cells in this are dates including Data!B7, Data$C7, R14 and
S14.

If P14 is "", it will be less than B7 < Data!B7, so this part does not
make sense? - But if P14 is not blank, if should test to see if P14
(the end date) is less than Data$B7 (the beginning of the fiscal
period that U14 is getting the # of days for) so you want the number
of days in this fiscal period to be zero.

What happens if you want to subtract 1/3/2007 from 15/2/2007? - The
validatation test mentioned below (If $O14 $P14 - "Start date must
be before end date") should make sure you don't get negative days.

Thanks for responding. I sure hope this makes sense and you can still
help.

Michele

On Sep 25, 1:26 pm, kassie wrote:

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- Hide quoted text -

- Show quoted text -