Forum Discussion
Calculate total working hours between date/time excluding weekends and break time
- Jul 15, 2024
I attached the wrong version, sorry.
The 2 hours in the first example is because the 1st of May is in the holiday list. If you remove that date from the holiday list, you get 3 hours.
See the attached version.
Create a list of public holidays.
Select the list, and name it Holidays.
Now let's say you have a start date/time in D2 and the corresponding end date/time in E2.
In another cell, for example E2, enter the formula =TimeDiff(D2, E2)
This can be filled down if required.
- VladLockhartJul 19, 2024Copper Contributorthank you so much. I can use it and it works just fine.
- HansVogelaarJul 18, 2024MVP
You hadn't defined a named range Holidays. I have removed Holidays from the code, and I have made it return 0 if the start and end dates are empty.
- VladLockhartJul 18, 2024Copper Contributor
HansVogelaar Here is the file idk why it doesn't work.
- HansVogelaarJul 17, 2024MVP
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
- VladLockhartJul 17, 2024Copper Contributor
HansVogelaar Hi i tried like this. Is this right? Why is it still error? What do u mean by making a list of holidays?