# MS Works Spreadsheet formula required

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

-
Do everybody a favor and don't use Micro\$oft Works... Use Apache OpenOffice!! (opinion) – BenjiWiebe Feb 16 '13 at 2:56
Why so complicated??? I'd just subtracted Jan,1 of Dec,31 for the respective year dates and checked the result. – Peter L. Feb 19 '13 at 18:46
Since it looks like you have the answers you need, some your own, please consider marking this question answered so people don't think you still need an answer. You can post your own answer and choose it if you like. – User5910 Sep 12 '13 at 18:50

I can't try this in Works but the following works in Excel--see if you can nest the ifs like so:
`=IF(E4="FEBRUARY", IF(H4="YES", IF(D4>28,"28 DAYS",""), IF(D4>29,"29 DAYS","")),"")`
I've simplified this by only looking at H4, assuming if H4 is not YES then it must be a leap year.

BTW is this a homework question?

EDIT: FWIW here's another way to do it with just three formulas and no need to list out the months or use your own calculation for leap years. Again, I tried this in Excel, I can't try it in Works:

A. Calculate the month number from the month name in `A6` with this formula. Note the months in the first {} section must be in alphabetical order and the month numbers in the second {} section must be in corresponding order:

`=LOOKUP(E4,{"April", "August", "December", "February", "January", "July", "June", "March", "May", "November"," October", "September"},{4,8,12,2,1,7,6,3,5,11,10,9})`

Caveat: This won't give good results if there's not a valid month in `E4`.

B. Calculate the number of days from the beginning of that month to the beginning of the next month in `B6`:

`=DATE(F4+TRUNC((A6+1)/12),MOD(A6+1,12),1)-DATE(F4,A6,1)`

This accounts for jumping from December to January in two places:

1. Year: Add one to the year if the month+1 (A6+1) is greater than 12 by using the TRUNC formula. If the calculated value is less than one (eg. 12/12=1) it truncates it to zero so you're in the same year.
2. Month: If A6+1 is greater than 12 start back at month 1. The MOD function here returns the remainder after dividing by 12, so 13 becomes 1. You could also use an IF function but this is more compact.

C. Test the number of days in `C6`:

`=IF(D4>B6,B6&" DAYS","")`

-
Sorry User5910. Thanks for your reply but it doesn't work. – marangeo Feb 18 '13 at 3:36
Hmm, what happens when you try it? It looks like nested ifs are ok in Works according to this article. – User5910 Feb 18 '13 at 4:49
My apologies User5910, your formula does indeed work. I must have entered something incorrectly but nonetheless a valid formula. However, I have modified your suggestion slightly, involving a couple of extra formulae: – marangeo Feb 18 '13 at 7:24
Sorry! the last comment was cut short. 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 – marangeo Feb 18 '13 at 9:07
FWIW those formulas will work in Excel too if you change `=IF(L7=1,...` to `=IF(L7=TRUE,...` or just `=IF(L7,...` – User5910 Feb 19 '13 at 18:33