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]

Review this

DATEDIF Function

The Excel DATEDIF function returns the difference between two date values in years, months, or days. The DATEDIF (Date + Dif) function is a "compatibility" function that comes from Lotus 1-2-3. Excel won't help you fill out the arguments for DATEDIF like other functions, but it will work when...

exceljet.net

 

[Macropod]

Simple: =A1-B1, where the dates are in A1 & B1, respectively, though to exclude the end date, you'd need =A1-B1-1. Conversely, to include the start date, use =A1-B1+1.

 

[dpaton05]

Once you have the number of days, is it possible to work out how many weekdays, Saturday's, Sunday's or public holidays are in that period. I am trying to make a spreadsheet that calculate a quote price for a period, with a start/end date and a start,/end time. There is difference price for the different types of days but also different prices for different times of the day.

Does anyone have any advice as to some direction with what method I should use to solve this problem?

 

[DanteAmor]

How about

Book1
	A	B	C	D
1	02/12/2019
2	28/12/2019
3	Days	Sunday's	Saturday's	weekdays
4	27	3	4	20
Sheet

Cell Formulas
Range	Formula
A4	A4	=A2-A1+1
B4	B4	=INT((WEEKDAY($A$1- 1)-$A$1+$A2)/7)
C4	C4	=INT((WEEKDAY($A$1- 2)-$A$1+$A2)/7)
D4	D4	=NETWORKDAYS(A1,A2)

More about WEEKDAY Function

WEEKDAY Function

The Excel WEEKDAY function takes a date and returns a number between 1-7 representing the day of week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable. You can use the WEEKDAY function inside other formulas to check the day of week.

exceljet.net

 

[dpaton05]

Thanks for that Dante, that is really helpful.

I did some testing with the above formulas and these are the variables I set

• Start Date - 10/12/19
• End Date - 14/12/19

The 10th is a tuesday and the 14th is a saturday

The networkdays function displays 4 which is correct but the function to work out the saturdays is 0. If I change the finish date to be the 15/12/19, the total number of sundays goes to 1 but the total saturdays remains at 0.

The formula I have to work out the number of saturdays is

=INT((WEEKDAY($B$17- 2)-$B$17+$F17)/7)

The formula I have to work out the number of sundays is

=INT((WEEKDAY($B$17- 1)-$B$17+$F17)/7)

 

[dpaton05]

The start date is in B17 and the end date is in F17

 

[dpaton05]

Could someone explain this formula to me please as I don't understand how it works and if I did understand it, I could use it for some additional applications?

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

 

[dpaton05]

It is also strange that if I change the start date from 10/12/19 to the 9/12/19, the saturday counter increases to 1, but the 9/12/19 is a monday???

 

[dpaton05]

Just got it to work I think using

=INT((WEEKDAY($B$17- 3)-$B$17+$F17)/7)

But it was only by trial and error.

Could you explain why it worked Dante please?