Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-27-2021, 12:04 AM
zanestein zanestein is offline Word VBA to find difference between two clock times and then also subtract additional minutes Windows 7 32bit Word VBA to find difference between two clock times and then also subtract additional minutes Office 2010 32bit
Novice
Word VBA to find difference between two clock times and then also subtract additional minutes
 
Join Date: May 2018
Posts: 4
zanestein is on a distinguished road
Default Word VBA to find difference between two clock times and then also subtract additional minutes

I am trying to create a VBA routine in a word table that will subtract a start time from and end time to get hours & minutes, then subtract an additional amount of minutes from the total.
For example, table cell E3 (contains end time) minus cell C3 (contains start time), then from the result, minus cell D3 (contains the amount of minutes taken for lunch). End result would be total amount of time worked during that time span.

I want to put the result in cell F3. I would then repeat this on each of the ten days in question, and then at the bottom sum total all of the F column data.

I have it set up right now to manually type in the F column on each date, and the document automatically adds each item to the total as it is typed. But I am hoping to set it up so the document can calculate the F cells based on entering the C, D and E data, and then total that for me.
Reply With Quote
  #2  
Old 05-27-2021, 01:02 AM
Guessed's Avatar
Guessed Guessed is offline Word VBA to find difference between two clock times and then also subtract additional minutes Windows 10 Word VBA to find difference between two clock times and then also subtract additional minutes Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Hmm, perhaps this will work for you.
1. Add two Date Picker Content Controls to your document. Give one the title "In" and the other one the title "Out". Format both so that they only show the time (scroll to the bottom of the format options and maybe pick h:mm:ss am/pm).
2. Add a third Plain Text Content Control and give it the title "Delta".
3. Then add the following macro to your ThisDocument module
Code:
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
  Dim tIn As Date, tOut As Date, tDelta As Date
  tIn = ActiveDocument.SelectContentControlsByTitle("In")(1).Range.Text
  tOut = ActiveDocument.SelectContentControlsByTitle("Out")(1).Range.Text
  tDelta = tOut - tIn
  
  ActiveDocument.SelectContentControlsByTitle("Delta")(1).Range.Text = Format(tDelta, "h:mm:ss")
End Sub
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
Reply

Tags
calculate, table cell, time difference

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Word VBA to find difference between two clock times and then also subtract additional minutes Microsoft 365 Word - Subtract Cells help SekiTimewalker Word Tables 2 05-11-2020 04:02 PM
How do you use the find and replace tool to find dates and times in Excel 2013? Jules90 Excel 3 04-14-2020 07:40 PM
Word VBA to find difference between two clock times and then also subtract additional minutes when using find in vba several times it stops working Salah Word VBA 1 03-02-2017 03:02 AM
Word VBA to find difference between two clock times and then also subtract additional minutes Automatic find difference between two data Kuannygohcheetatt Excel 2 02-10-2015 09:02 AM
Word VBA to find difference between two clock times and then also subtract additional minutes Microsoft Word Clock Rude Awakening Word 2 10-16-2013 07:14 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:17 AM.


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