How to calculate the date difference excluding weekend and public holidays for specific date format

Question

I have the following date format , but I can't use these date format to calculate the date difference excluding weekends and holiday. The two date is input as 01-Oct-2024 and 15-Oct-2024. The result should be 9. However, when I use excel function network's, it returns #VALUE. HOW TO FIX IT?

Answer 1

I entered the two dates exactly as you specified. Both were converted automatically to date values. The NETWORKDAYS function produced the value 11. Adding 14-Oct as a holiday reduced that value to 10. Why do you think it should be 9? Remember, the dates are inclusive. The ten working days are 1, 2, 3, 4, 7, 8, 9, 10, 11, and 15.

Answer 2

Hi @nononame2021

Excel can't treat 01-Oct-2024 as date, how to fix and calculate date difference?

How did you get this date data? Was it a formula result, entered manually, copied and pasted from other files, or something else?

If you click the cell include "01-Oct-2024", what will you find in formula bar?

Please make sure Excel read it as a date format, not a text format. You may right click the cell > Format Cells > Date, choose the correct date format.

Besides, as Barry Schwarz replied, the NETWORKDAYS function produced the value 11.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---