| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated Cell Formula
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated Cell Formula
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated Cell Formula
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 - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated Cell Formula
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated Cell Formula
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated Cell Formula
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?",I F(AND(O14<B7,P14B7,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 "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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated Cell Formula
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<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 "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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated Cell Formula
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated Cell Formula
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 -- 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 ... 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated Cell Formula
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. -- 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I have a complicated formula that I need SERIOUS help with please! | Excel Discussion (Misc queries) | |||
| Complicated Formula - I think | Excel Worksheet Functions | |||
| complicated if then formula...need help | Excel Discussion (Misc queries) | |||
| Complicated IF Formula | Excel Worksheet Functions | |||
| Complicated Formula | Excel Discussion (Misc queries) | |||
Linear Mode
