Can you count the days between two dates with a formula

[dpaton05]

How do you count the number of days between two dates, entered into 2 separate cells using a formula in excel?

 

[DanteAmor]

Sorry, For Saturday's try this:

=INT((WEEKDAY($A$1- 7)-$A$1+$A2)/7)

09-dic​	Monday	2​
10-dic​	Tuesday	3​
11-dic​	Wednesday	4​
12-dic​	Thursday	5​
13-dic​	Friday	6​
14-dic​	Saturday	7​	use -7
15-dic​	Sunday	1​	use -1

 

[dpaton05]

Thanks Dante,

What do I do for public holidays?
I have a list of public holidays in a range.

 

[DanteAmor]

Try. Change G1:G5 for the range of holidays

=NETWORKDAYS(A1,A2,G1:G5)

 

[dpaton05]

Thanks again Dante. Just a few more issues.

1. I have the start date of 21/12/19 and the end date of the 25/12/19. These two cells are B17 and F17
2. In that period there is 1 saturday , 1 sunday, 2 week days and 1 public holiday
3. On my counters, it says there are: (Formulas are next)
   1. 2 public holidays (=NETWORKDAYS(B17,F17,Sheet2!$G$87:$DO$97))
   2. 3 weekdays (=NETWORKDAYS(B17,F17))
   3. 1 saturday (=INT((WEEKDAY($B$17- 7)-$B$17+$F17)/7))
   4. 1 sunday (=INT((WEEKDAY($B$17- 1)-$B$17+$F17)/7))

The range of those cells with the holiday dates do not contain any of the dates between 21/12/19 and 25/12/19 except 25/12/19


Thanks for this help Dante

 

[DanteAmor]

Try this

Book1
	A	B	C	D	E	F	G	H
17		21/12/2019				25/12/2019		Holidays
18	holdays	1						25/12/2019
19	weekdays	2						01/01/2019
20	Saturday's	1
21	Sunday's	1
Hoja1

Cell Formulas
Range	Formula
B18	B18	=NETWORKDAYS(B17,F17)-NETWORKDAYS(B17,F17,H18:H20)
B19	B19	=NETWORKDAYS(B17,F17,H18:H20)
B20	B20	=INT((WEEKDAY(B17-7)-B17+F17)/7)
B21	B21	=INT((WEEKDAY(B17-7)-B17+F17)/7)

Review this:

NETWORKDAYS Function

The Excel NETWORKDAYS function calculates the number of working days between two dates. NETWORKDAYS automatically excludes weekends (Saturday and Sunday) and can optionally exclude a list of holidays supplied as dates.

exceljet.net

 

[dpaton05]

Thanks Dante,

Do I need to change something for the weekend cells if there is a public holiday on a sat or sun?

 

[DanteAmor]

Thanks Dante,

Do I need to change something for the weekend cells if there is a public holiday on a sat or sun?

How do you want to count it as a weekend or as a holiday?

 

[StephenCrump]

It's the number of Sundays between dates in A1 and A2, counting both end dates.

=INT((A2-A1)/7) will give you number of whole weeks between A1 and A2. Let's call this NumWeeks. If there is a fraction of a week left over, you may need to add 1 depending on where Sundays fall.

You might want to consider each case:
If A1 is a Sunday, =WEEKDAY(A1-1) is 7, so your formula will always evaluate to NumWeeks + 1.
If A1 is a Saturday, =WEEKDAY(A1-1) is 6, so your formula will evaluate to NumWeeks + 1 if MOD(A2-A1,7) is 1 or greater, i.e. A2 is anything but a Saturday
If A1 is a Friday, =WEEKDAY(A1-1) is 5, so your formula will evaluate to NumWeeks + 1 if MOD(A2-A1,7) is 2 or greater, i.e. A2 is anything but a Friday or a Saturday
...
If A1 is a Monday, =WEEKDAY(A1-1) is 1, so your formula will evaluate to NumWeeks + 1 if MOD(A2-A1,7) is 6 or greater, i.e. a Sunday only.

 

[DanteAmor]

Thanks Dante,

Do I need to change something for the weekend cells if there is a public holiday on a sat or sun?

You should not change anything, Saturdays and Sundays are non-working days:

Book1
	A	B	C	D	E	F	G	H
17		21/12/2019				25/12/2019		Holidays
18	holdays	1						21/12/2019
19	weekdays	2						22/12/2019
20	Saturday's	1						25/12/2019
21	Sunday's	1
Sheet

Cell Formulas
Range	Formula
B18	B18	=NETWORKDAYS(B17,F17)-NETWORKDAYS(B17,F17,H18:H20)
B19	B19	=NETWORKDAYS(B17,F17,H18:H20)
B20	B20	=INT((WEEKDAY(B17-7)-B17+F17)/7)
B21	B21	=INT((WEEKDAY(B17-7)-B17+F17)/7)

 

[dpaton05]

Thanks Dante, you are a legend!!