Will have a look when I get home this afternoon (SA time), and let you know.
--
Hth

Kassie Kasselman
Change xxx to hotmail


"mjones" wrote:

On Sep 27, 12:38 am, kassie wrote:
OK , this should do the trick! The problem was that you did not have values
in S14:T14, and I assumed you would have. Sum fortunately sorts out empty
cells!

=IF(AND(O14="",P14=""),0,IF(P14<B7,0,IF(AND(O14="" ,P14<""),"Start?",IF(AND-(P14="",O14<""),"End?",IF(AND(O14<B7,P14B7,P14<C 7),P14-O14-SUM(R14:T14),"-")))))

I am sure you are aware that I left out Data! in the Col B and C formulae?

--
Hth

Kassie Kasselman
Change xxx to hotmail



"mjones" wrote:
On Sep 26, 12:46 am, kassie wrote:
The formula you are looking for is
=IF(AND(O14="",P14=""),0,IF(P14<B7,0,IF(AND(O14="" ,P14<""),"Need Start
date",IF(AND(P14="",O14<""),"Need end
date",IF(AND(O14<B7,P14B7,P14<C7),P14-O14-R14-S14-T14,"")))))

I must apologise!!!! I have become so used to people asking question
without detail, that I never read past your problem!!! My face was quite red
when I eventually read your message to the end. I must congratulate you on
the way in which you explained your problem, except the typo :)
The data validation is straight forward, and I'll get back to you on the CF

--
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 -

Hi Kassie,

Thank you so much for responding. Please, no appologies. We have to
skim text in these days of information overload.

I think my brain has turned to mush trying to figure this one out.

The formula only partially works so I made a test spreadsheet at
www.quality-computing.com/numberofdaystest.xls

I sure hope you can help.

Michele- Hide quoted text -

- Show quoted text -

Hi Kassie,

Well, we're getting there, but still not quite right. The formula
wasn't working so I made some changes, but not I'm getting 365 days if
there isn't a full year.

I've updated a new file at www.quality-computing.com/numberofdaystest.xls

In S14, the formula says IF(AND(O14<B5,P14C5),365 ...

but P14 is not greater than C5 (15-May-08 is not greater than 31-
Mar-09)

Also, the data validation won't work at all. I'm wonder if it has to
do with the macro (just realized I have one and the government hasn't
complained yet so I guess it's okay). Maybe I have to validate the
start and end date in the macro. Do you have any idea how to do
that? Learning VBA is on my long list of to dos, but hasn't hit the
top yet.

Here the validation requirement:
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"

I truly appreciate your help on this tricky one.

Thanks,

Michele