|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
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 |
Tags |
calculate, table cell, time difference |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
when using find in vba several times it stops working | Salah | Word VBA | 1 | 03-02-2017 03:02 AM |
Automatic find difference between two data | Kuannygohcheetatt | Excel | 2 | 02-10-2015 09:02 AM |
Microsoft Word Clock | Rude Awakening | Word | 2 | 10-16-2013 07:14 AM |