Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

The difference between 2 days

Copper Contributor

I'm trying to count the difference between 2 days, but excluded weekends
I try with function networkdays but it always count the starting date. When I add -1 to the function, it start to count the progress from the same day is -1

any better function to do it?

7 Replies

Hi @Gumzz212,

To calculate the difference between two dates, excluding weekends and excluding the starting date, in Excel using the NETWORKDAYS function, you can use the following formula:

 

=NETWORKDAYS(start_date + 2, end_date)

 

 

Where:

  • start_date is the start date.
  • end_date is the end date.

This formula will add two days to the start date before calculating the number of workdays between the two dates. This will effectively exclude the starting date from the calculation and exclude the weekend that the starting date may fall on.

For example, if you want to calculate the difference between 2023-10-23 and 2023-10-28, excluding weekends and excluding the starting date, you would use the following formula:

 

=NETWORKDAYS("2023-10-23" + 2, "2023-10-28")

 

 

This formula would return the value 4, which is the number of business days between the two dates, excluding the starting date and the weekend that the starting date falls on.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.


If the post was useful in other ways, please consider giving it Like.


Kindest regards,


Leon Pavesic
(LinkedIn)

May I tell you what i want?
There is a fixed date that cannot be changed, I want this date to be a benchmark for the next date
The next date is a date that can change, it would be better if it was filled in before the fixed date but it's okay if it was filled in after the fixed date but don't take too long
As example
The fixed date/deadline was: 18 oct 2023
The change date : either before 18 oct 2023 or after
If it filled at 17 oct 2023 the count is -1
if it filled at 18 oct 2023 the count is 0
if it filled at 19 oct 2023 the count is 1
of course the count is excluding weekend
appreciate if you can help me

@Gumzz212 

Does this do what you want?

HansVogelaar_0-1698236810090.png

Fixed date in D2, variable date in E2, difference in F2:

=IF(E2>=D2,NETWORKDAYS(D2,E2)-1,1-NETWORKDAYS(E2,D2))

i think you have the same question being addressed in another posting. My alternative there was:
=NETWORKDAYS(A2,B2)+(2*(A2>B2)-1)*NETWORKDAYS(A2,A2)
The 1st part being the straight networkdays difference the second part is the +/-1 if the first day is counted in the networkdays

@Gumzz212 

Please avoid asking the same question multiple times - it causes unnecessary duplication of work.

okay thanks for your suggestion
will not doing it again