Date fields, counting how many after a specified date???? 2

[xnay]

Good afternoon all,

I've been trying for the last couple of days to figure out in Word2K3 the syntax I need to have a field outside a of table add up how many date fields located within a table column are after a specified date:

ie. "text1" (this field need to add how many dates are after 2007-01-01)


"2007-01-15" (this filed is within a table column)

"2007-01-07" (this filed is within a table column)

"2006-12-22" (this filed is within a table column) do not want to count this field

"2006-12-14" (this filed is within a table column) do not want to count this field


I only want to add the first two dates above. Each field within the Column is a date field with its own unique bookmark name. Hope that clarifies it a little??



Is there a way to group the Fields within the Column?? if so, sample code please??

Am I S.O.L.??, if so....&%#$*^! AAAAAArg! Thanks anyway

Have a good one, all

 

[macropod]

Hi xnay,

You need a formula field coded like:

{={IF{REF Dt1 \@ yyyyMMdd}>= 20070101 {=Val1} 0}+{IF{REF Dt2 \@ yyyyMMdd}>= 20070101 {=Val2} 0}+{IF{REF Dt3 \@ yyyyMMdd}>= 20070101 {=Val3} 0}+{IF{REF Dt4 \@ yyyyMMdd}>= 20070101 {=Val4} 0}}

where 'Dt1'-'Dt4' are bookmarks representing the dates and 'Val1'-'Val4' are bookmarks representing the values.

Alternatively, you could use a formula field coded like:

{={IF{REF Dt1 \@ yyyyMMdd}>= 20070101 {=SUM(Tbl B1)} 0}+{IF{REF Dt2 \@ yyyyMMdd}>= 20070101 {=SUM(Tbl B2)} 0}+{IF{REF Dt3 \@ yyyyMMdd}>= 20070101 {=SUM(Tbl B3)} 0}+{IF{REF Dt4 \@ yyyyMMdd}>= 20070101 {=SUM(Tbl B4)} 0}}

where 'Dt1'-'Dt4' are bookmarks representing the dates, B1-B4 being the value cell references and 'Tbl' is a bookmark for the table as a whole.

All those braces (ie '{ }'), but not the brackets (ie '( )') are created in pairs via Ctrl-F9. If you create just

]{={IF{REF Dt1 \@ yyyyMMdd}>= 20070101 {=Val1} 0}

or

{IF{REF Dt1 \@ yyyyMMdd}>= 20070101 {=SUM(Tbl B1)}

you can copy & paste this three times, then edit the bookmark & cell references, before adding the operators and a final Ctrl-F9 to complete the outer field.

Note: you can't use cell references for the dates - you must use bookmarks - and bookmarks in table cells must not include the cell markers.

Cheers

[MS MVP - Word]

 

[xnay]

Wow, now that's what I call some sample code, and it all make perfect (logical... not common) sense. Thank you macropod.

Recently, while searching for code to figure this one out on my own, I came across an interesting page at gmayor.com, and whose code are they showcasing to display a date properly, non other than the great macropod's. Your name preceeeds you, that's very impressive considering the vastness of internet resources... Props, my fellow coder.... Props It's a priveledge to get YOUR response, and to work with YOUR code.

I havn't had the time to implement the code yet, and I'll be sure to mention my success afterwards, but I see now in your code what was eluding me before. I'm gaining a better understanding on how bookmarks really work because of your code. It's only been a week since I found out that Word has fields ("WOW!", I said to myself. Where the hell have I been???) and I've been frustratingly happily (now that's grammar) tweaking documents since.

Thanks again macropod, it's nice to know people with your expertise are just a Few clicks Away.

Sincerely.

 

[macropod]

You're welcome

[MS MVP - Word]

 

[xnay]

Hello macropod,
I'm back with a couple of questions. I'm having a bit of trouble using the REF command. I'm not sure if I'm setting up my BM's (bookmarks)correct?? I've spent the last week trying to figure this out.... (I'm even having dreams of it)without any success. Maybe you can help shed a little light on the situation.


I'm a bit confused when you say "and 'Val1'-'Val4' are bookmarks representing the values." AND "B1-B4 being the value cell references" Are these not the same as BM'ing the date itself?? Please, elaborate on this for me, I don't quite understand what you mean here??

My table is BM'ed as a whole as "Tbl" (are the cell markers after each row to be included in this BM??)

I've Bm'ed the contents (2007-01-10) within column cell A:A, excluding the cell marker, as "Dt1" (without the quotes)

My calculation field (outside of the table) always treats my date as an addition problem or errors

ie {= Dt1 \@ yyyyMMdd} results in 1996

ie {= Ref Dt1 \@ yyyyMMdd} results in !Syntax Error, A:A


I'M MISSING SOMETHING SOMEWHERE, and I'm sure it has to do with those value BM's you mentioned

Thanks again macropod

 

[macropod]

Hi xnay,

Re:

'Val1'-'Val4' are bookmarks representing the values." AND "B1-B4 being the value cell references" Are these not the same as BM'ing the date itself??

I gave you two ways of dealing with the problem. One involved bookmarking the individual values (ie "'Val1'-'Val4' are bookmarks representing the values"); the other involved bookmarking the whole table and using cell references (ie "B1-B4 being the value cell references and 'Tbl' is a bookmark for the table as a whole"). Neither of these applies to the date, which must be bookmarked (I suggested 'Dt1'-'Dt4').

Re:

My calculation field (outside of the table) always treats my date as an addition problem or errors
...
I'M MISSING SOMETHING SOMEWHERE

Yes, you are missing something - you haven't coded the date references as shown -
{REF Dt1 \@ yyyyMMdd}
is not the same as:
{= Dt1 \@ yyyyMMdd}
or
{= Ref Dt1 \@ yyyyMMdd}
neither of which will work.

Code as shown and it will work - I know because I tested it before posting.

Cheers

[MS MVP - Word]

 

[xnay]

Macropod, I'm grateful for your patience and speedy reply. After I read your post, I thought to myself that I just typo'ed in my last post when showing the code. So, while mumbling to myself how sure I was that I had tried it that way, I gave you the benefit of doubt and typed 'er up... 'exactly as shown'. I didn't modify any bookmarks or anything. My jaw dropped after hitting F9... to make room for my foot! A date appeared like magic. Wow! I can't believe I racked my brain for a week and didn't see the obvious. It was difficult for me to inquire upon you again because I was sure that your first post had everything I needed... and then some. I'll end this time feeling good that I was at least right about that.

Many thanks to a true MVP