Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Sep 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time calculation (office 2007)

    Could someone please help me? I apologize for my ignorance, but I really could use some help with a project i am working on for my service department. I am trying to create a work order template in word, and I need to have a start time, a finish time, and a total hours, to display like this: Start Time: 8:15 AM End Time: 4:30 PM Total Hours: 8.25 I am not sure how to achieve this. I am fairly inexperienced in word and vb and not sure how to achieve this. Any help would be greatly appreciated.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Time calculation (office 2007)

    Are you creating a protected form, in other words, a form where users can only enter text in certain parts of the form through form fields? This article describes the general process of creating a protected form in Word 2007: Create forms that users complete in Word - Word @ Microsoft Office Online.

    Word does not have a specific form field for times, and its fields do not have a built-in feature to format times. However, you can attach code to fields that calculates differences or reformats them after the user exits the field.

    You could use a drop-down control for times (if the list would not be too painfully long), and this would simplify the task to recalculating the elapsed time after the user changes the drop-down.

    Alternately, you could have users click a button to enter times in a dialog (UserForm). This would give you much more control, but also is more work to design.

    Does any of that sound as though it is on the right track? It's unfortunate that Word lacks Excel's features in this regard, where you can simply pre-define the format of a cell or a formula, and the user just has the type in the value and those changes and calculations happen automatically.

  3. #3
    New Lounger
    Join Date
    Sep 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time calculation (office 2007)

    Yes I have created a protected form. And yes I think you understand what i am looking for. Again, sorry for my ignorance but could you explain how I would do this one, "Alternately, you could have users click a button to enter times in a dialog (UserForm). This would give you much more control, but also is more work to design." Thanks in advance

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Time calculation (office 2007)

    I tried to figure out how to do this with Word 2007's new Content Controls and I couldn't figure it out. Here is an example with the "legacy" textbox controls and a "legacy" ActiveX button. It needs code for validation, i.e., to confirm that the user input is sensible.
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Sep 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time calculation (office 2007)

    THAT IS EXACTLY WHAT I WAS LOOKING FOR!!!!!!!!!!!! THANK YOU SO MUCH!

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Time calculation (office 2007)

    Hi jamles12,

    Here's a different version, that doesn't require any macros or generate a macro warning. This may be beneficial, depending on how your user's system security is configured.

    You can input times in 12hr or 24hr format, though you'll need to add 'pm' for 12hr times from 12 noon onwards.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Time calculation (office 2007)

    See macropod's example. I stand corrected on "Word ... fields do not have a built-in feature to format times."

  8. #8
    New Lounger
    Join Date
    Sep 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time calculation (office 2007)

    Ok thanks. that works even better, except what do i need to do to display total hours in whole numbers ie. 4:00 am - 5:30 am = 1.5 hrs? instead of 1:30

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Time calculation (office 2007)

    Hi jamles12,

    That's even simpler than the version i posted before! Try the attached.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    New Lounger
    Join Date
    Sep 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time calculation (office 2007)

    You are my hero! thanks sir.

  11. #11
    New Lounger
    Join Date
    Sep 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time calculation (office 2007)

    sorry, i accidentally posted it to my self the first time. (how about this one. I have a formula set up to give me total dollars, now i have a legacy checkbox and i need to make it so when i place a mark in the check box it automatically discounts the total and makes it say
    -(minus) whatever the total is, so i can show a warranty discount. Any help would be greatly appreciated. thanks)

  12. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Time calculation (office 2007)

    Hi jamles12,

    With the checkbox, you'll need to use an 'on exit' macro to trigger the calculation. That wouldn't be necessary if you used a DropDown formfield with Yes/No options or something equivalent. For the 'on exit' macro, we'd need the specifics of the checkbox name and the formfield/bookmark range names to be referenced, plus those to be updated, plus the details of the calculation.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  13. #13
    New Lounger
    Join Date
    Sep 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time calculation (office 2007)

    the yes no, would work fine, but im not sure how that would be any different. both would require a macro on exit wouldnt it? i have attached the template that i have been working on so you can see exactly what im looking for. if yes or a check mark next to warranty is selected, i need it to look to the sub total and place that amount negative in the discount box, so that the total reflects 0.

    thanks,

    Jamie
    Attached Files Attached Files

  14. #14
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Time calculation (office 2007)

    <P ID="edit" class=small>(Edited by macropod on 25-Sep-08 14:31. Corrected error in field coding)</P>Hi Jamie,

    I've had a look at your document, but I don't see a checkbox/dropdown formfield for applying the discount, or a field to show the discount value. I do see an empty cell next to the heading 'Warrranty Discount:', though, and you could:
    . insert a text formfield there for the user to input the discount %; or
    . insert a dropdown formfield there for the user to choose the Yes/No response.
    In either case, using the 'calculate on exit' property for the formfield is all you'd need to calculate the discounted value.

    For example, if you insert a text formfield:
    . Type = Number
    . formatted =0.00%
    . name = 'Discount'
    . 'Calculate on exit' checked,
    for the user to input the discount %, the 'Total' field calculation would become {=total*(1-Discount) # $,0.00}

    Alternatively, insert a dropdown formfield:
    . Dropdown menu items = Yes & No
    . name = 'Discount'
    . 'Calculate on exit' checked,
    for the user to input the discount %, the 'Total' field calculation would become {=total*(1-10%*{IF{Discount}= "Yes" 1 0}) # $,0.00}
    where the '10%' is a fixed discount rate and the field brace pairs (ie '{ }') are created via Ctrl-F9.

    There are various other ways you could approach this; including having the value of the discount displayed as a result of the % or Yes/No respionses. It's up to you. In no case is a macro required. This also raises the question of why you're using a dotm template - if you're not using macros or ActiveX controls, a dotx would suffice - and if you're not relying on Word 2007 features, even a plain dot template (which provides compatability for Word 97-2003) would do.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  15. #15
    New Lounger
    Join Date
    Sep 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time calculation (office 2007)

    I was over thinking it. thank you. Usually it is either 100 percent discount or 0 so i was just trying to make it so it would apply a 100 percent discount when i clicked the warranty check box. and vice versa. But this will work just fine. As far as the macro thing, I am running a macro to generate new work order numbers, because i needed it to discard the number if i didnt save the document because sometimes the work order gets canceled before its finished and with that macro it will just get rid of the number it generated and use it again next time. Thats the only way i could find to do it. thanks again for all of the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •