marangeo
Feb 13, 2013, 10:59 PM
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
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