MS Works Spreadsheet formula required

Forums: M S Works, Spreadsheet
Email this Topic • Print this Page

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="YES"),"28 DAYS","") and
=IF(AND(D4>29,E4="FEBRUARY",I4="YES"),"29 DAYS","")
Any help appreciated. Thanks

Topic Stats
Top Replies
Link to this Topic

Type: Question • Score: 0 • Views: 704 • Replies: 5

No top replies

@marangeo,

I think you're on the right track, and you could potentially create fewer formulas (although they would be more complicated) by combining the formulas for all of the months with 30 days (April, June, September and November) and combining the formulas for all of the months with 31 days (January, March, May, July, August, October and December).

Possibly something like this -

=IF(AND(D4>31,E4="APRIL", "JUNE","SEPTEMBER","NOVEMBER"),"30 DAYS","")
I'm not certain how the conditional works. In Excel, it would include wrapping the original statement in an OR statement.

Or would you need to create a short separate list of 30-day and 31-day months and then have the formula reference the list? I am unsure as I haven't used Works in a long, long time, but it might work to have the months in lists and then reference them that way, and the February issue could potentially be solved in a similar fashion, with lists of leap years giving you your times when the exception would kick in (e. g. just list 1996, 2000, 2004, 2008, 2012, 2016, etc. as you need them, or possibly even an anchor year - maybe 2000 and then also include a year formula whereby the exception only kicks in if the year, if subtracted from the anchor year, gives you a difference that is a multiple of 4).

1 Reply

@jespah,

jespah
Thanks for your reply - very much appreciated.
I'll take note and work through what you have suggested.

0 Replies

jespah

I resolved the "February" situation by taking the two 'And' functions separately
=AND(D4>28,E4="FEBRUARY",H4="YES
=AND(D4>29,E4="FEBRUARY",I4="YES"
and puting the results in cells L7 and M7 respectively then, using a nested 'If' function
=IF(L7=1,"28 DAYS",IF(M7=1,"29 DAYS",""))
and puting the result in cell A12 alongside the month of February.

MS Works Spreadsheet formula required

Quick Links

My Account

able2know