Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-27-2017, 09:15 AM
StephenRay StephenRay is offline Calculate document completion time Windows 7 64bit Calculate document completion time Office 2010 64bit
Advanced Beginner
Calculate document completion time
 
Join Date: Jan 2012
Location: Overland Park, Kansas
Posts: 53
StephenRay is on a distinguished road
Default Calculate document completion time

GMayor, your Code for this is Great. Thanks again.
GMaxey, I must learn how to step through the code. This is in my book. There is so much to learn!
Yes, I have been reading about Range, it is a method and object. I wrote one more macro, and used your oRng ! That part works but I am hung up on the format of DATE.
Perhaps you can save me hours and hours.

I have a date on the top line of my document that looks like this:
CRQ 1234567 Add Contractor None 7/27/2017 9:52:31 AM
I use this code to write it to the document:
Selection.InsertDateTime DateTimeFormat:="M/d/yyyy h:mm:ss am/pm", _
InsertAsField:=False, DateLanguage:=wdEnglishUS, CalendarType:= _


wdCalendarWestern, InsertAsFullWidth:=False

When I am finished with a piece of work, I want to know how many hours, minutes, and seconds it takes to complete. So I wrote this Macro:
Code:
Sub Macro45()
'
' Macro45 Macro
'
'This first part finds the date on the first line of the document and copies it
    Selection.HomeKey Unit:=wdStory
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "/"      ‘Finds the Date
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    Selection.MoveLeft Unit:=wdWord, Count:=2
    Selection.EndKey Unit:=wdLine, Extend:=wdExtend
    Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
    Dim oRng As Range
    Set oRng = Selection.Range
    oRng.Copy    
    
    
   
    Dim dtDuration As Date
    Duration = DateDiff("s", oRng, Time)  'This is only the seconds
    Selection.EndKey Unit:=wdStory
    Selection.TypeText Text:="It took this long." & Duration
End Sub
Reply With Quote
  #2  
Old 07-27-2017, 02:29 PM
gmaxey gmaxey is offline Calculate document completion time Windows 7 32bit Calculate document completion time Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,598
gmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nice
Default

You should have probably started a new thread. Perhaps Paul (macropod) or one of the others who knows how will move this to a new thread.

You may not need this much functionality but here it is anyway:

Code:
Sub Macro45()
Dim oRng As Word.Range
  Set oRng = ActiveDocument.Range
  With oRng.Find
    .Text = "[0-9]{1,2}\/"
    .MatchWildcards = True
    If .Execute Then
      oRng.End = oRng.Paragraphs(1).Range.End - 1
      If IsDate(oRng.Text) Then
        ActiveDocument.Range.InsertAfter "It took this long: " & fcnCalcSpanStart_Finish(oRng.Text, Now)
      End If
    End If
  End With
lbl_Exit:
  Exit Sub
End Sub
Function fcnCalcSpanStart_Finish(oDateStart As Date, oDateNow As Date)
'A VBA Function coded by Greg Maxey, http://gregmaxey.com/word_tips.html, 7/27/2017
Dim lngYear As Long, lngMonth As Long, lngDay As Long, lngHour As Long, lngMinute As Long, lngSecond As Long
Dim lngIndex As Long
Dim oDateLDPM As Date, oDateLDIM As Date, oAnchorDate As Date
Dim dtdHMSCalc As Date
Dim strHMS As String
Dim arrStringParts() As String
  If oDateStart > oDateNow Then
    fcnCalcSpanStart_Finish = "The start date passed much be prior to date now."
    Exit Function
  End If
  'Calculate complete years passed.
  If Year(oDateNow) > Year(oDateStart) Then
    'A different calendar year. Has one or more complete years passed?
    If Month(oDateNow) = Month(oDateStart) Then
      'Same month in subsequent year. Check day.
      If Day(oDateNow) >= Day(oDateStart) Then
        'Complete year passed
        lngYear = DateDiff("yyyy", oDateStart, oDateNow)
      Else
        lngYear = DateDiff("yyyy", oDateStart, oDateNow) - 1
      End If
    ElseIf Month(oDateNow) > Month(oDateStart) Then
      'Complete year passed.
      lngYear = DateDiff("yyyy", oDateStart, oDateNow)
    Else
      lngYear = DateDiff("yyyy", oDateStart, oDateNow) - 1
    End If
  Else
    'Obviously no commplete year passed.
    lngYear = 0
  End If
  'Calculate full months passed from last full year.
  lngMonth = (DateDiff("m", DateSerial(Year(oDateStart), Month(oDateStart), 1), _
              DateSerial(Year(oDateNow), Month(oDateNow), 1)) + IIf(Day(oDateNow) >= Day(oDateStart), 0, -1)) Mod 12
  'Calculate number of days passed from last full month.
  If Day(oDateNow) >= Day(oDateStart) Then
    lngDay = Day(oDateNow) - Day(oDateStart)
  Else
    'Calculate for end of month.
    'Get date on last day of previous month.
    oDateLDPM = DateSerial(Year(oDateNow), Month(oDateNow), 0)
    'Get date on last day of index month.
    oDateLDIM = DateSerial(Year(oDateNow), Month(oDateNow) + 1, 0)
    oAnchorDate = DateSerial(Year(oDateNow), Month(oDateNow) - 1, Day(oDateStart))
    If oDateLDIM = oDateNow Then
      If lngMonth = 11 Then
        'Reset month and add a year.
        lngMonth = 0
        lngYear = lngYear + 1
      Else
        lngMonth = lngMonth + 1
      End If
    Else
      lngDay = DateDiff("d", IIf(oAnchorDate > oDateLDPM, oDateLDPM, oAnchorDate), oDateNow)
    End If
  End If
  'Calculate hours, minutes and seconds.
  If TimeValue(oDateStart) > TimeValue(oDateNow) Then lngDay = lngDay - 1
  dtdHMSCalc = oDateNow - oDateStart
  lngHour = Hour(dtdHMSCalc)
  lngMinute = Minute(dtdHMSCalc)
  lngSecond = Second(dtdHMSCalc)
  
  'Format returned value.
  fcnCalcSpanStart_Finish = lngYear & IIf(lngYear = 1, " year, ", " years, ") & lngMonth & IIf(lngMonth = 1, " month, ", _
                 " months, ") & lngDay & IIf(lngDay = 1, " day", " days")
  fcnCalcSpanStart_Finish = Replace(fcnCalcSpanStart_Finish, "0 years", "")
  fcnCalcSpanStart_Finish = Replace(fcnCalcSpanStart_Finish, ", 0 months", "")
  fcnCalcSpanStart_Finish = Replace(fcnCalcSpanStart_Finish, ", 0 days", "")
  strHMS = lngHour & IIf(lngHour = 1, " hour, ", " hours, ") & lngMinute & IIf(lngMinute = 1, " minute, ", _
                 " minutes, ") & lngSecond & IIf(lngSecond = 1, " second", " seconds")
      
  If fcnCalcSpanStart_Finish <> vbNullString Then
    fcnCalcSpanStart_Finish = fcnCalcSpanStart_Finish & ", " & strHMS
  Else
    fcnCalcSpanStart_Finish = strHMS
  End If
  fcnCalcSpanStart_Finish = Replace(fcnCalcSpanStart_Finish, "0 hours", "")
  fcnCalcSpanStart_Finish = Replace(fcnCalcSpanStart_Finish, ", 0 minutes", "")
  fcnCalcSpanStart_Finish = Replace(fcnCalcSpanStart_Finish, ", 0 seconds", "")
  arrStringParts = Split(fcnCalcSpanStart_Finish, ", ")
   If UBound(arrStringParts) > 0 Then
     If UBound(arrStringParts) = 1 Then
       fcnCalcSpanStart_Finish = Replace(fcnCalcSpanStart_Finish, ", ", " and ")
     Else
       fcnCalcSpanStart_Finish = vbNullString
       For lngIndex = 0 To UBound(arrStringParts)
         Select Case True
           Case lngIndex <= UBound(arrStringParts) - 2
             fcnCalcSpanStart_Finish = fcnCalcSpanStart_Finish & arrStringParts(lngIndex) & ", "
           Case lngIndex <= UBound(arrStringParts) - 1
             fcnCalcSpanStart_Finish = fcnCalcSpanStart_Finish & arrStringParts(lngIndex) & " and "
           Case Else
             fcnCalcSpanStart_Finish = fcnCalcSpanStart_Finish & arrStringParts(lngIndex) & "."
         End Select
       Next lngIndex
     End If
    End If
lbl_Exit:
  Exit Function
End Function
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #3  
Old 07-27-2017, 03:43 PM
StephenRay StephenRay is offline Calculate document completion time Windows 7 64bit Calculate document completion time Office 2010 64bit
Advanced Beginner
Calculate document completion time
 
Join Date: Jan 2012
Location: Overland Park, Kansas
Posts: 53
StephenRay is on a distinguished road
Default

GMaxey, Thanks for your reply. I am at home now. I read enough last night about Range to know you made a range in the first paragraph, that is the first line of the document.
As for the rest, I must go study it.
I am learning a lot.
Reply With Quote
  #4  
Old 07-27-2017, 03:44 PM
macropod's Avatar
macropod macropod is offline Calculate document completion time Windows 7 64bit Calculate document completion time Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,363
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by gmaxey View Post
You should have probably started a new thread. Perhaps Paul (macropod) or one of the others who knows how will move this to a new thread.
Done.

Split from: https://www.msofficeforums.com/word-...-saturday.html
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 07-27-2017, 05:34 PM
StephenRay StephenRay is offline Calculate document completion time Windows 7 64bit Calculate document completion time Office 2010 64bit
Advanced Beginner
Calculate document completion time
 
Join Date: Jan 2012
Location: Overland Park, Kansas
Posts: 53
StephenRay is on a distinguished road
Default

Omigosh GMaxey, I ask for some help fixing a flat tire on my old Chevrolet and you give me a Rolls Royce! You must have 30 years experience at this!
This works like a dream!
Reply With Quote
  #6  
Old 07-27-2017, 05:50 PM
gmaxey gmaxey is offline Calculate document completion time Windows 7 32bit Calculate document completion time Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,598
gmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nice
Default

Glad to help.

Actually I set a range to the active document, then looked for something that looks like the start of a date, then moved the end of the found range to the end of its paragraph (not including the mark), then verified it was a date.

As for the function. I didn't do all of that on a lark today. I've tinkered with code to return a calculated age based on a birthday but never took it past years, months and days. Today I groomed that code to drill down to seconds.

It may appear classy but I sit in a pile of scalp and bloody hair. I am not the whiz kid with dates and math functions. Not quite 30 but almost 20.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #7  
Old 07-27-2017, 05:55 PM
macropod's Avatar
macropod macropod is offline Calculate document completion time Windows 7 64bit Calculate document completion time Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,363
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Question: Are you getting any time difference from what you'd get with-
Code:
Sub Test()
MsgBox Format(ActiveDocument.BuiltInDocumentProperties("Total Editing Time") / 1440, "[d] hh:mm")
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 07-27-2017, 06:01 PM
gmaxey gmaxey is offline Calculate document completion time Windows 7 32bit Calculate document completion time Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,598
gmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nice
Default

Paul,

I can't answer for the OP as I while I thought about that I got lost in trying to keep my old function from returning 1 day + some number of hours, minutes, seconds when the total time span was less than 24 hours. One would think that your one liner would give a more accurate response to the amount of time actually working on a document.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #9  
Old 07-29-2017, 04:50 AM
StephenRay StephenRay is offline Calculate document completion time Windows 7 64bit Calculate document completion time Office 2010 64bit
Advanced Beginner
Calculate document completion time
 
Join Date: Jan 2012
Location: Overland Park, Kansas
Posts: 53
StephenRay is on a distinguished road
Default

Macropod, I will try that with a Message Box. I have never tried code for a Message Box but I am anxious to try. Actually I intend on starting a new thread concerning combining all my simple Macros with Message Boxes.
With help from this forum, I played with VBA last week a lot. I can do more than two times the work using Macros. Nobody knows about Macros at work.
It is early in the morning, everybody is still sleeping and I have lots of yard work I want to do while it is still cool. My green beans are coming up, I have to get some chicken wire fence to protect the plants from the rabbits. I was snaring rabbits but the police told me to stop that.
Thanks guys!
Reply With Quote
  #10  
Old 07-30-2017, 02:43 PM
macropod's Avatar
macropod macropod is offline Calculate document completion time Windows 7 64bit Calculate document completion time Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,363
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The message box was used just to simplify the demo. The more important issue is whether the method (using a Built-In Document Property to get the "Total Editing Time") is just as good as all the circumlocution you're currently using. The output could just as easily be output to the document itself. Not only that, there's also a DOCPROPERTY field that will output the same output wherever you want it to appear, in minutes, without the need for a macro at all!
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 07-31-2017, 07:29 AM
StephenRay StephenRay is offline Calculate document completion time Windows 7 64bit Calculate document completion time Office 2010 64bit
Advanced Beginner
Calculate document completion time
 
Join Date: Jan 2012
Location: Overland Park, Kansas
Posts: 53
StephenRay is on a distinguished road
Default

Macropod, I have worked with your code:
MsgBox Format(ActiveDocument.BuiltInDocumentProperties("T otal Editing Time") / 1440, "[d] hh:mm")

It gives about the same number of minutes and seconds.
It seems to start as soon as you open a new document. The clock starts ticking as soon as you open a new document.
I am convinced it is accurate too.

However, with my work, I open a new document and may not start on a piece of work for several minutes or may an hour later. I am having so much fun with the ease of my work using macros and I marvel at the time savings; so I get everything ready to go ahead of time. Everything is set up before I start on a piece of work. I have to balance accuracy and speed, but the speed part is thrilling. Without Macros, one piece of work takes around 30 minutes of fast, intense work. With Macros my fastest time is 8 minutes and 19 seconds! Of course some of these pieces of work require telephone calls and other research to clarify the data; and this research may greatly increase the time.

And I have it rigged so that when I get a piece of work and start to work on it, the very first thing that happens is that I copy and paste an identifying Number using a macro that also grabs the Now time That's when the time starts.

Nevertheless, if I didn't already have the other code with lots of logic and code already set up I know I would use your code.

I have never studied Microsoft Word very much, I learned enough just to be able to write a document with some shapes and arrows. So I am surprised that anything exists such as your (ActiveDocument.BuiltInDocumentProperties("Total Editing Time") / 1440, "[d] hh:mm")

But I did recently buy a good book "Word 2016 IN DEPTH" by Faithe Wempen, a QUE Book. I bought it to compliment my studies of VBA. On page 39-43 Document Properties are discussed, including the Statistics Tab.

This is all very interesting. I must look this over to see if I might ever want to use anymore of these.
Reply With Quote
  #12  
Old 07-31-2017, 04:11 PM
macropod's Avatar
macropod macropod is offline Calculate document completion time Windows 7 64bit Calculate document completion time Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,363
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The 'Total Editing Time' time starts from when you open the document until you close it with saved changes. What this means is that you can open & close a document without making edits and the stats don't change. It also means you can open & close a document several times, making & saving edits as you go, and only the open time will be counted.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #13  
Old 08-01-2017, 07:54 AM
StephenRay StephenRay is offline Calculate document completion time Windows 7 64bit Calculate document completion time Office 2010 64bit
Advanced Beginner
Calculate document completion time
 
Join Date: Jan 2012
Location: Overland Park, Kansas
Posts: 53
StephenRay is on a distinguished road
Default

Macropod, I wondered why the time did not change when I ran the code several times in a row! Ha! It must be because I did not change or edit it.
Reply With Quote
  #14  
Old 08-10-2017, 07:55 AM
StephenRay StephenRay is offline Calculate document completion time Windows 7 64bit Calculate document completion time Office 2010 64bit
Advanced Beginner
Calculate document completion time
 
Join Date: Jan 2012
Location: Overland Park, Kansas
Posts: 53
StephenRay is on a distinguished road
Default

GMaxey, This calculation between two dates is working great. Others may be interested to see what the output looks like:
It took this long: 1 day, 22 hours, 27 minutes and 30 seconds. Thursday, August 10, 2017 9:48:13 AM

But usually, I get something like this:
It took this long: 10 minutes and 15 seconds. Thursday, August 10, 2017 9:45:49 AM

The date that it subtracts from looks something like this: 8/8/2017 11:20:42 AM
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate time over two dates Diver Excel 3 07-22-2017 11:31 PM
Calculate document completion time how to calculate average time aymanharake Excel 6 02-17-2017 04:22 PM
Calculate Time in Hours and Percent pabyford Excel 6 12-13-2016 12:29 PM
Calculate document completion time Formula To Calculate Countdown Days To Project Completion Catherine Project 1 02-19-2014 11:15 AM
Calculate document completion time How to get cells to calculate time? jrasche2003@yahoo.com Excel 2 02-09-2007 07:10 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:04 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft