I am creating an MS Works - Version 8.5 - Spreadsheet into which is entered the Date of the month, the Month and the Year. The year is calculated to be either a Non-Leap Year or a Leap Year according to a formula, and the word "YES" or the word "NO" appears under the respective heading.
The headings are, Date of month - D3, Month - E3, Year - F3, Non-Leap Year - H3 and Leap Year - I3
The data is entered in D4, E4, F4 and the word "YES" or the word "NO" appears under the respective heading in H4 and I4.
The months, January to December are listed in B11 to B22.
What I want to show, in Column A, alongside each month, is a message to signal an error, should an incorrect date of the month be entered - e.g 32 for January, 31 for April etc. Whether the year is a Non-Leap Year or a Leap Year makes no difference and the word "YES" or the word "NO" does not come into the equation, so the following formulae produce the desired result.
=IF(AND(D4>31,E4="JANUARY"),"31 DAYS","")
=IF(AND(D4>31,E4="APRIL"),"30 DAYS","")
February, however, which can have either 28 days or 29 days, and, because each cell can hold only one formula, presents a different situation. The formula will have to be a combination of:
=IF(AND(D4>28,E4="FEBRUARY",H4=",H4="),"28 DAYS"),"","FEBRUARY",I4=") and
=IF(AND(D4>29,E4="),"29 DAYS",I4="")
Any help appreciated. Thanks

Post a workbook and I'll take a look. Make sure there is enough data to demonstrate all your variations.

I'm not sure you actually need the leap year columns. We should be able to flag the maximum days of any month given by the month and year alone.

I don't think any of this is necessary, because Works doesn't allow date errors as long as you format a cell as a date. Is it necessary for what you are doing to have dates in 3 columns?

Quote:

---

Originally Posted by JBeaucaire

Post a workbook and I'll take a look. Make sure there is enough data to demonstrate all your variations.

I'm not sure you actually need the leap year columns. We should be able to flag the maximum days of any month given by the month and year alone.

---

Thanks for your reply.

Hope this is what you mean. Since only *.xls files may be attached, this is the MS Works Spreadsheet (*.xlr) file saved as Excel 97-2003 (*.xls). It appears to work OK. Some slight differences in the functions and formulae but the same result.

Quote:

---

Originally Posted by joypulv

I don't think any of this is necessary, because Works doesn't allow date errors as long as you format a cell as a date. Is it necessary for what you are doing to have dates in 3 columns?

---

Thanks for your reply.

In this case, Yes.

I'm a bit flummoxed at looking at all of this, what in the world could this be for?

Can you state in normal sentence form what you're trying to accomplish, the end goal? Don't speak in Excel-speak or MSWorks-speak. In English, what is the point of all this? I can't imagine it can't be done in one simple formula instead of 8 long steps...

Quote:

---

Originally Posted by JBeaucaire

I'm a bit flummoxed at looking at all of this, what in the world could this be for?

Can you state in normal sentence form what you're trying to accomplish, the end goal? Don't speak in Excel-speak or MSWorks-speak. In English, what is the point of all this? I can't imagine it can't be done in one simple formula instead of 8 long steps...

---

Thanks for your reply.

The whole idea is to determine the day of the week in any given year in the Gregorian calendar e.g "On which day was Christmas 1848?"

Answer: Monday

Problem solved. Used a couple of extra formulae using the 'And' function:
=AND(D4>28,E4="FEBRUARY",H4="YES") in cell L7
=AND(D4>29,E4="FEBRUARY",I4="YES") in cell M7
then a nested 'If' function
=IF(L7=1,"28 DAYS",IF(M7=1,"29 DAYS","")) in cell A12