Complicated Cell Formula

[mjones]

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

 

[Guest]

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?

 

[mjones]

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


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?

 

[Guest]

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,P14>B7,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

 

[mjones]

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,P14>B7,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
















- 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

 

[Guest]

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,P14>B7,P14<C7),P14-O14-SUM(R14:T14),"")))))

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

 

[mjones]

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,P14>B7,P14<C7),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











- 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,P14>C5),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

 

[Guest]

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

 

[Guest]

Hi Michelle,

The data validation is overridden by the macro. If you disable the macro,
validation will work, except that you must select "between" and not "not
between".

The formula? That works perfectly. It is true that P14 is not greater than
C5 (15-May-08 is not greater than 31- Mar-09), but then C5-O14-SUM(R14:R14)
comes into play, as that constitutes the FALSE part, and that happens to
equate to 365. If I can make a suggestion here, either upload a file with
actual data, or let us communicate privately, if you do not want everybody
peeping. My address is easy to decipher for a lady of your capabilities!

Regards

 

[Guest]

I have modified your calendar1_Click() routine, to allow for data validation
in VBA. Of course, you can change the prompt, as well as the default dates,
as you prefer, even making the stat date = today's date? Else, remove the
macro's, if these are not allowed ;-(, and then use the normal data
validation function. the calender works so nicely though, that I would keep
that if I were you, even if you have to get digital certification.

Private Sub Calendar1_Click()

If Application.Range("O14") Then
If Calendar1.Value < Range("B4").Value Then
MsgBox ("Start date must be after " & Range("B4").Value)
ActiveCell.Value = Range("B4").Value
ActiveCell.Offset(1, 0).Select
Exit Sub
Else
If Application.Range("P14") Then
If Calendar1.Value > Range("C7").Value Then
MsgBox ("End date must be before " & Range("C7").Value)
ActiveCell.Value = Range("C7").Value
ActiveCell.Offset(1, 0).Select
Exit Sub
End If
End If
End If
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "d-mmm-yy"
ActiveCell.Offset(1, 0).Select
End If
End Sub

The activecell.Offset(1,0) I brought in to get rid of the calender as soon
as a selection is made.