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