In the following script
It showing that the symbol checkdate is already difined differently
Can someone please go through that and correct the script
Dim tempDate As Date 'Declare tempDate as a Date variable'
Function IsHoliday(checkDate As Date) As Boolean
Dim ws As Object
Dim holidayCell As Object
Dim dayOfWeek As Integer
Dim nextSaturday As Date
' Assuming your festival holiday dates are in column A of the 'FestivalHolidays' sheet, starting from row 2 '
On Error Resume Next
Set ws = ThisComponent.Sheets.getByName("FestivalHolidays")
On Error GoTo 0
If ws Is Nothing Then
IsHoliday = False ' No holiday sheet found, treat all days as working days
Exit Function
End If
' Get the day of the week (Sunday = 0, Monday = 1, ..., Saturday = 6) '
dayOfWeek = Weekday(checkDate, 2)
' Check if it's a Sunday or a festival holiday '
If dayOfWeek = 1 Or Not IsEmpty(ws.getCellByPosition(0, checkDate.Day)) Then
IsHoliday = True
Exit Function
End If
' Check if it's the 3rd Saturday of the month '
If dayOfWeek = 6 And checkDate.Day > 15 Then
tempDate = DateSerial(Year(checkDate), Month(checkDate) + 1, 1)
nextSaturday = tempDate - (Weekday(tempDate, 2) + 7 - 6) ' Find the first Saturday of the next month'
If checkDate.Day = nextSaturday.Day - 14 Then
IsHoliday = True
Exit Function
End If
End If
IsHoliday = False ' If no match is found'
End Function
Function CalculateWorkingDays(startDate As Date, endDate As Date) As Integer
Dim totalDays As Integer
Dim currentDate As Date
totalDays = 0
currentDate = startDate
While currentDate <= endDate
If Not IsHoliday(currentDate) Then
totalDays = totalDays + 1
End If
currentDate = currentDate + 1
Wend
CalculateWorkingDays = totalDays
End Function
Sub CalculateTotalAndIndividualWorkingDays()
Dim ws As Object
Dim lastRow As Integer
Dim totalDays As Integer
totalDays = 0
' Assuming your data is in Sheet1 from row 1 to 1500 '
Set ws = ThisComponent.Sheets.getByName("Sheet1")
lastRow = ws.getRows().getCount()
For i = 1 To lastRow
If Not IsEmpty(ws.getCellByPosition(0, i).getValue()) And Not IsEmpty(ws.getCellByPosition(1, i).getValue()) Then
If ws.getCellByPosition(0, i).getValue() = ws.getCellByPosition(1, i).getValue() Then
ws.getCellByPosition(2, i).setValue(0)
Else
ws.getCellByPosition(2, i).setValue(CalculateWorkingDays(ws.getCellByPosition(0, i).getValue(), ws.getCellByPosition(1, i).getValue()))
End If
totalDays = totalDays + ws.getCellByPosition(2, i).getValue()
End If
Next i
ws.getCellByPosition(4, 1).setString("Total Number of Working Days") ' Place the total label in cell E1 '
ws.getCellByPosition(4, 2).setValue(totalDays) ' Place the total in cell E2 '
ws.getCellByPosition(2, 1).setString("Working Days") ' Place the label in cell C1 '
For i = 2 To lastRow
If Not IsEmpty(ws.getCellByPosition(0, i).getValue()) And Not IsEmpty(ws.getCellByPosition(1, i).getValue()) Then
ws.getCellByPosition(2, i).setValue(CalculateWorkingDays(ws.getCellByPosition(0, i).getValue(), ws.getCellByPosition(1, i).getValue()))
End If
Next i
End Sub
Chat gpt has given me this code