#1
|
|||
|
|||
Count number of days
Hi
I am trying to create a leave form for my company im microsoft word and have created two date fields for commencing and end. I need to find a day to put the number of days between these dates excluding fridays in another text field is there a way to do that |
#2
|
|||
|
|||
Assuming leave start and leave end dates are entered in a datepicker type content control then something like this shoud work:
Code:
Sub Test() Dim oCCS As ContentControl Dim oCCE As ContentControl Dim lngDays As Long Set oCCS = ActiveDocument.SelectContentControlsByTitle("Start").Item(1) Set oCCE = ActiveDocument.SelectContentControlsByTitle("End").Item(1) MsgBox fcnCalcDays(oCCS.Range, oCCE.Range) lbl_Exit: Exit Sub End Sub Function fcnCalcDays(Date1 As Range, Date2 As Range) As Long Dim lngDays As Long Dim lngIndex As Long Dim dTest As Date lngDays = 0 dTest = Date1.Text For lngIndex = 1 To DateDiff("d", Date1.Text, Date2.Text) If Weekday(dTest, vbUseSystemDayOfWeek) <> 6 Then lngDays = lngDays + 1 End If dTest = DateAdd("d", 1, dTest) Next fcnCalcDays = lngDays lbl_Exit: Exit Function End Function |
#3
|
||||
|
||||
Expanding from Greg's code. If you add a plain text content control titled 'Days' then put the following in the ThisDocument module of the document, the calculation will be automatic.
Code:
Option Explicit Private Sub Document_ContentControlOnEnter(ByVal ContentControl As ContentControl) Dim oCCS As ContentControl Dim oCCE As ContentControl Dim oCCD As ContentControl Set oCCS = ActiveDocument.SelectContentControlsByTitle("Start").Item(1) Set oCCE = ActiveDocument.SelectContentControlsByTitle("End").Item(1) Set oCCD = ActiveDocument.SelectContentControlsByTitle("Days").Item(1) Select Case ContentControl.Title Case "Start" If oCCD.ShowingPlaceholderText = False And oCCE.ShowingPlaceholderText = False Then MsgBox "Make your change then click outside the field to update the day count." End If Case "End" If oCCE.ShowingPlaceholderText = False And oCCE.ShowingPlaceholderText = False Then MsgBox "Make your change then click outside the field to update the day count." End If If oCCS.ShowingPlaceholderText = True Then MsgBox "Complete the start date field before clicking here" GoTo lbl_Exit End If Case "Days" If oCCS.ShowingPlaceholderText = True Or oCCE.ShowingPlaceholderText = True Then MsgBox "Complete both date fields before clicking here" GoTo lbl_Exit Else oCCD.Range.Text = CStr(fcnCalcDays(oCCS.Range, oCCE.Range)) End If Case Else End Select lbl_Exit: Exit Sub End Sub Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean) Dim oCCS As ContentControl Dim oCCE As ContentControl Dim oCCD As ContentControl Set oCCS = ActiveDocument.SelectContentControlsByTitle("Start").Item(1) Set oCCE = ActiveDocument.SelectContentControlsByTitle("End").Item(1) Set oCCD = ActiveDocument.SelectContentControlsByTitle("Days").Item(1) Select Case ContentControl.Title Case "Start" If oCCD.ShowingPlaceholderText = False And oCCE.ShowingPlaceholderText = False Then oCCD.Range.Text = CStr(fcnCalcDays(oCCS.Range, oCCE.Range)) End If If Val(oCCE.Range.Text) < Val(oCCS.Range.Text) And _ oCCE.ShowingPlaceholderText = False Then MsgBox "The start date is later than the end date?" oCCE.Range.Text = "" oCCD.Range.Text = "" GoTo lbl_Exit End If Case "End" If Val(oCCE.Range.Text) < Val(oCCS.Range.Text) And _ oCCS.ShowingPlaceholderText = False Then MsgBox "The start date is later than the end date?" oCCE.Range.Text = "" oCCD.Range.Text = "" GoTo lbl_Exit End If If oCCD.ShowingPlaceholderText = False And oCCS.ShowingPlaceholderText = False Then oCCD.Range.Text = CStr(fcnCalcDays(oCCS.Range, oCCE.Range)) End If Case Else End Select lbl_Exit: Set oCCS = Nothing Set oCCE = Nothing Set oCCD = Nothing Exit Sub End Sub Function fcnCalcDays(Date1 As Range, Date2 As Range) As Long Dim lngDays As Long Dim lngIndex As Long Dim dTest As Date lngDays = 0 dTest = Date1.Text For lngIndex = 1 To DateDiff("d", Date1.Text, Date2.Text) If Weekday(dTest, vbUseSystemDayOfWeek) <> 6 Then lngDays = lngDays + 1 End If dTest = DateAdd("d", 1, dTest) Next fcnCalcDays = lngDays lbl_Exit: Set oCCS = Nothing Set oCCE = Nothing Set oCCD = Nothing Exit Function End Function Sub ClearDateFields() Dim oCCS As ContentControl Dim oCCE As ContentControl Dim oCCD As ContentControl Set oCCS = ActiveDocument.SelectContentControlsByTitle("Start").Item(1) Set oCCE = ActiveDocument.SelectContentControlsByTitle("End").Item(1) Set oCCD = ActiveDocument.SelectContentControlsByTitle("Days").Item(1) oCCD.Range.Text = "" oCCE.Range.Text = "" oCCS.Range.Text = "" lbl_Exit: Set oCCS = Nothing Set oCCE = Nothing Set oCCD = Nothing Exit Sub End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#4
|
|||
|
|||
Thank you for your reply it seems i have missed something and both options are not working i have attached sample is it possible someone to insert the code so i can view where i went wrong
thanks a million |
#5
|
||||
|
||||
Try the attached.
I note your document had some macros referencing formfields, though none was present. You really shouldn't used formfields and content controls in the same document. They weren't designed to be used that way and doing so can cause problems.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
||||
|
||||
The attached includes the code I posted and I have changed the content controls to make it work. For inserting content controls see https://www.gmayor.com/insert_content_control_addin.htm
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Inserting a formula to count 30 days before and after a date in WORD table | bjh894 | Word | 9 | 05-18-2020 11:14 AM |
count a number of cells based on the beginning of a order number | Kubi | Excel | 2 | 08-06-2017 08:54 PM |
Locking number of days in a project | Mdon1 | Project | 0 | 06-10-2017 02:40 AM |
Assigning number of days | MarcF | Project | 3 | 07-16-2016 10:44 AM |
How to count even exact date 90 days | redza | Excel | 1 | 09-17-2013 03:36 AM |