#1
|
|||
|
|||
Help needed: Table Formulas with AND
This may sound odd... I'm building a document with an annual calendar that the user can update for any year.
I'm building the February Calendar, and I have determined a check for leap years, 0 for false 1 for true, bookmarked as Leap2. I'm now down to the bottom row of the month. I want something that similar to the following, given that G8 = 28 and Leap2 = 1: { IF {=G8 }>= 28 { IF {=AND({=G8}=28, {=Leap2}=1)}=1 {=G8+1} "" } { IF { =G8} 0 {=G8+1} "" } } Now, my issue is purely with the syntax of using AND in this capacity. If this formula doesn't make sense, I'd appreciate a few examples of table formulas using IF and AND. Thanks in advance! |
#2
|
||||
|
||||
To do the leap year calculation correctly, you'll need a field coded like:
{QUOTE{SET Yr {DATE \@ YYYY}}{SET Mo {DATE \@ M}}{=({=G8}=28)*((MOD(Yr,4)=0)+(MOD(Yr,400)=0)-(MOD(Yr,100)=0))*(Mo=2)*29 \# 0;;}} In the above, '*' functions as an AND condition and '+' functions as an OR condition. To see how to do just about everything else you might want to do with dates in Word, check out my Microsoft Word Date Calculation Tutorial, at: http://windowssecrets.com/forums/sho...ation-Tutorial or http://www.gmayor.com/downloads.htm#Third_party Do read the document's introductory material.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Interesting...
I always find strange ways of doing things. I determined leap year using the following method (items are bookmarked): UserYear = 2016 (or any year the user inputs) Leap1 = 1/29/UserYear Leap2 = { If {Leap1 \@ MMMM } = "February" 1 0 } So, maybe something like the following should work? { IF {=G8 } >= 31 { IF { { =G8 } =28 }*{ { =Leap2 } = 1} 29 "" } {IF { =G8 } ≠ 0 { =G8+1 } "" }} Or maybe? { IF {({ =G8})*({ =Leap2})} =28 29 "" } For some reason, I can't seem to get this to work. It always produces a 29 result; it appears as though =28 is an answer rather than a qualifier. "≠" because I couldn't use greater/less than symbols in forum. That said, I'll look at what you've provided. I can see that it does the most comprehensive check. I'm just not confident in my ability to incorporate it properly. Thanks! Last edited by Scrapyard; 01-25-2014 at 04:50 AM. Reason: Another Attempt |
#4
|
||||
|
||||
The formula I posted will work fine for whatever the current year is - you don't need separate fields to determine whether the year is a leap year. using that formula for building calendars for different years, you could either:
• delete {SET Yr {DATE \@ YYYY}} and change all the Yr references to UserYear; or • change {SET Yr {DATE \@ YYYY}} to {SET Yr UserYear} Similarly, depending on how you're determining that the month being worked with is February, you could change or delete the {SET Mo {DATE \@ M}} and *(Mo=2) parts of the formula.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
formula, table |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel Formulas | barryjones | Excel | 2 | 06-03-2013 04:27 AM |
Need help with formulas please | paul_pearson | Excel | 0 | 03-20-2013 06:51 AM |
Using subtotals in Pivot table formulas | ninfanger | Excel | 3 | 10-23-2010 12:29 AM |
IF Formulas | mizzamzz | Excel | 1 | 07-08-2010 02:32 AM |
Table formulas | markg2 | Word Tables | 0 | 01-15-2010 06:49 AM |