# Creating macro for calculating total number of working days in a given date range

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

Anything wrong with using WORKDAY() ?

Not me, as I donâ€™t know any rules on 3rd Saturdaysâ€¦ So maybe forget my suggestion to use internal WORKDAY(), if you have special needs.
.

First would be to check your complete code, where are definitions of checkdate, or just try to use in this function gptCheckDate to avoid colliding with other code.

1 Like

so ask chatgpt for further help

1 Like

Not very good advice: judging by the code, this particular chatgpt is really bad - not only will it not be able to explain what needs to be fixed, it most likely will not even be able to explain what exactly it suggested. Perhaps some other chat (out of many thousands created today) would give a working code, but this is not certain.

@saikumarnvr56 Welcome!
I understand that you have a problem and want to solve it with our help. Tell exactly about your task, and not about the problems of code that does nothing. As I understand it, you are trying to automate the calculation of Diwali (Deepavali, Divali) and think that have not yet created ready-made tools for this? Have you tried to ask not chatGPT, but, for example, Google?

1 Like

Exactly, the code is very badâ†’
If the people together with chatgpt create some junk poetry, then they should please continue to work alone with it, and not post here without comment so that we fix the crap again!

Oh, you mean it!.. You must have misread this questionâ€¦ Try it differently: sit comfortably, relax, read line by line and smile, because people who have been learning to write correct code all their lives will not be out of work for a very long time

1 Like

Here is my requirement
In columnA im having lot creation date and in column B Iâ€™m having lot clearance dates
In column C i need the total number of working days taken to clear the lot ( all Sundays and 3rd Saturday is holiday,it should substract those days,if exist in between the date range, in sheet2 column a ,i wrote list of holidays (dates) ) it should substract the holiday also)

And I want the sim of all values in column C at some place

It is important! Is this an Excel workbook? Or did you make a typo and it will be a Calc spreadsheet? And this spreadsheet will be processed in LibreOffice?
The point is that the macro function solution cannot be used in different file formats without a very large amount of additional effort.
It will be good if you attach a small sample of data to the next comment - it is much easier to experiment with a ready-made table than with its textual description.

1 Like

@Wanderer is right, you donâ€™t need to write a macro to solve this problem - the built-in functions of Calc (and maybe Excel too) will handle this calculation easier and faster.
In fact, letâ€™s break this problem into separate parts.
If you subtract the value of column A from column B, then you get all the days, right? Now you can use COUNTIFS() function to count how many days mentioned on Sheet2 fall within that date range and simply subtract that number from the total number of days. So youâ€™ll exclude said holidays, right?
Now letâ€™s deal with the days off - all Sundays and third Saturdays. It could be difficult if you try to calculate them. But you can do it differently! Treat these days like HOLIDAYS! Just add all these dates to the Sheet2 and the formula will return you the correct value. Isnâ€™t it true?

If you understand the idea, then try the formula `=NETWORKDAYS(A2;B2;Sheet2.\$A\$1:\$A\$65;{1;0;0;0;0;0;0})`

2 Likes

Thank you so much for your response
Here Iâ€™m attaching one of the worksheet for which I have to calculate the working days taken for clearing the lot
Untitled 1.xlsx (18.1 KB)

In that sheet1 is having date ranges (they are creation dates in column A and clearance dates in column B )

As I told earlier if there are any holidays it should substract them
If lot creation date and clearance dates are same then as no. Of days taken to clear the lot is zero

Every month im counting the days Manually and adding them Manually,it is taking too much of time and takimg away patience from me
I request you to please check this and suggest me how could I automate this process

I got it like this:

``````=IF(A2="";"";IF(A2=B2;0;NETWORKDAYS.INTL(A2;B2;11;FestivalHolidays.\$A\$1:\$A\$27)))
=IF(A2="";"";COUNTIFS(\$FestivalHolidays.A\$1:A\$27;">="&A2;\$FestivalHolidays.A\$1:A\$27;"<"&(B2+1))+NETWORKDAYS(A2;B2;\$FestivalHolidays.A\$1:A\$27;{0;1;1;1;1;1;1}))
``````

Check, please - Example.xlsx (41.4 KB)

Yes, the formulas turned out to be long, but in any case, itâ€™s a little shorter than the code from ChatGPT, right?

2 Likes

Thank you so much
That networkdays.intl formula is working

Thanks a lot for helping me

1 Like