[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Agenda time calculations in Word

Posted on 2014-01-21
9
Medium Priority
?
839 Views
Last Modified: 2014-01-24
Group,

I it possible to add fields to an agenda template so that if the starting time is inputted, and times for each topic are added, an end time for each topic could be calculated?

Thanks,

Michael
0
Comment
Question by:Michael Paxton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 13

Expert Comment

by:akb
ID: 39798321
I'm sure it is. Can you post a sample spreadsheet so we can better understand what you are trying to achieve?
0
 

Author Comment

by:Michael Paxton
ID: 39798352
I'd like to be able to put a start time for the meeting, and have the amount of time for each topic update the end time, and have the last end time roll up to the meeting end time.  Sound reasonable?
Agenda-Template.docx
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39798637
You can insert a formula field in a table cell { = SUM(ABOVE) }.
Select the last cell in a column, choose Formula on the Layout tab and type
= SUM(ABOVE) into the dialogue.
Note that to update the total, press F9 when the field is in the selection.

Other field solutions that do not total columns can use bookmarked locations or Excel-like cell notation.

A VBA method with Content Controls would also be possible.
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
LVL 15

Expert Comment

by:DrTribos
ID: 39799579
The agenda document may require some reformatting to make the template suitable for different types of agendas.  Times would have to be put in as minutes (as seems to be the current practice) or hours with fractions... (e.g. 1.5 hrs, 1.75 hrs) for the calculations you would not be able to mix minute format with hour format...

I'd suggest the approach implied by Grahams comment - the agenda items would need to be in a table and then sum up the table...  there would be quite a few calculations hidden in the document if you want to calculate the running time.  

Also if you were distributing to others PDF might be worth considering... (don't want people breaking the word document).

I think you can do this but it might be fiddly to use if using field codes.  The other option as per Grahams suggestion is VBA - if you are able to go down that path I think you can cut out a lot of complexity and make the overall document easier to work with.

Hope this helps,
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39799736
A major point that I missed.

Word fields do not have a Date/Time calculation method. This link shows the complexity of using fields to calculate a given number of days ahead:
http://www.gmayor.com/insert_a_date_other_than_today.htm

Time-only calculations might be a bit simpler, but I can't think of a way to separate a piece of text like 11:30 into 11 and 30. The example above uses CreateDate three times to get each part of the current date. It doesn't start from text, so could use an arbitrary date.

This means that you will have to use VBA. The following code expects a ContentControl in cells 2 and 3 of each row in the table, except for the first (header) and last (total) row.
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Dim tbl As Table
    Dim r As Integer
    Dim rw As Row
    Dim strTime1 As String
    Dim strTime2 As String
    Dim dtTime1 As Date
    Dim dtTime2 As Date
    Dim dtTotal As Date
    Dim dtDuration As Date
    
    If ContentControl.Range.Tables.Count = 1 Then
        Set tbl = ContentControl.Range.Tables(1)
        For r = 2 To tbl.Rows.Count - 1
            Set rw = tbl.Rows(r)
            strTime1 = rw.Cells(2).Range.ContentControls(1).Range.Text
            strTime2 = rw.Cells(3).Range.ContentControls(1).Range.Text
            rw.Cells(4).Range.Text = ""
            If IsDate(strTime1) Then
                dtTime1 = CDate(strTime1)
                If IsDate(strTime2) Then
                    dtTime2 = CDate(strTime2)
                    dtDuration = dtTime2 - dtTime1
                    rw.Cells(4).Range.Text = Format(dtDuration, "HH:nn:ss")
                    dtTotal = dtTotal + dtDuration
                End If
            End If
        Next r
        If CDbl(dtTotal) = 0 Then
            tbl.Rows.Last.Cells(4).Range.Text = ""
        Else
            tbl.Rows.Last.Cells(4).Range.Text = Format(dtDuration, "HH:nn:ss")
        End If
    End If
End Sub

Open in new window



If have modified the table in your document down to four rows for demonstration purposes and added Content Controls and the macro above in the ThisDocument module.

Note that the file extension should be put back to .docm after you have downloaded it.
Agenda-Template.docx
0
 

Author Comment

by:Michael Paxton
ID: 39799928
Graham,

I am having problems downloading and opening the file, even after changing the file extension to docm.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39800275
Alternative approach....

Does your agenda have substantial formatting that would preclude from performing the whole thing in Excel rather than Word.

A cell in excel can be set fairly wide and deep to accommodate multiple lines of text and then the time calculations would be much simpler.

Duration doesn't have to be added in true time format if you don't wish, it can use literal numbers so 45 would be 45 minutes or 00:45 if viewed in time format.

The link for finish time would also then be a lot simpler.

Thanks
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39800299
Example attached.
Excel-agenda.xlsx
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 39800734
paxtonm,

No idea what problem you might be getting, however you can use your own version of the document.

Put a Content Control into each cell of columns 2 and 3 - except for the first and the last rows - reserved for header and totals. Copy the macro into the ThisDocument module of your document. It must go there to hook the Document_ContentControlOnExit event. Otherwise  you will have to run the macro code manually.

Try entering times into one or more rows of columns 2 and 3 in the format hh:mm. When valid times are found, the cell column 4 will be filled in and the total should appear in cell 4of the last row.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question