Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Ms works spreadsheet (https://www.askmehelpdesk.com/showthread.php?t=734102)

  • Feb 13, 2013, 10:59 PM
    marangeo
    Ms works spreadsheet
    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
  • Feb 14, 2013, 01:38 AM
    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.
  • Feb 14, 2013, 04:50 AM
    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?
  • Feb 14, 2013, 08:40 PM
    marangeo
    1 Attachment(s)
    Quote:

    Originally Posted by JBeaucaire View Post
    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.
  • Feb 14, 2013, 08:58 PM
    marangeo
    Quote:

    Originally Posted by joypulv View Post
    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.
  • Feb 15, 2013, 12:59 AM
    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...
  • Feb 15, 2013, 02:21 AM
    marangeo
    Quote:

    Originally Posted by JBeaucaire View Post
    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
  • Feb 18, 2013, 01:54 AM
    marangeo
    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

  • All times are GMT -7. The time now is 08:57 AM.