finding number of sundays and saturdays between tow dates include hours

[abdi1]

hi....i have tow dates in my excel sheet in A2 and A3 which have format ( MM/DD/YYYY HH:MM)
how to find the number Sundays and Saturdays between thous tow dates ...
iv google many pages but all i found have only MM/DD/YYYY format without HH:MM
any help is appreciated

 

[jtakw]

Hi,

I don't think the Cell formatting with Time has anything to do with it:

Book3.xlsx
	A	B	C
1			Inclusive
2	01/02/2021 08:15		27
3	04/03/2021 17:00
Sheet889

Cell Formulas
Range	Formula
C2	C2	=DAYS(A3,A2)-NETWORKDAYS(A2,A3)+1

 

[abdi1]

Hi,

I don't think the Cell formatting with Time has anything to do with it:

Book3.xlsx
	A	B	C
1			Inclusive
2	01/02/2021 08:15		27
3	04/03/2021 17:00
Sheet889

Cell Formulas
Range	Formula
C2	C2	=DAYS(A3,A2)-NETWORKDAYS(A2,A3)+1

hi thanks for you jtakw
unfortunately i dont have DAYS function in my office......2007

 

[jtakw]

Then try this:

Book3.xlsx
	A	B	C	D
1			Inclusive	Or
2	01/02/2021 08:15		27	27
3	04/03/2021 17:00
Sheet889

Cell Formulas
Range	Formula
C2	C2	=INT(A3-A2-NETWORKDAYS(A2,A3)+1)
D2	D2	=INT(A3)-INT(A2)-NETWORKDAYS(A2,A3)+1

 

[abdi1]

i forgut to say
reason for my thread is that i need to count the number of hours between tow dates....one time including Saturdays and Sundays and the other excluding Saturdays and Sundays

 

[jtakw]

Well, that's not what your OP says.

 

[abdi1]

Well, that's not what your OP says.

I Know ...but this is for more explanation of my purpose from this thread my frind

any way your excel function works very good..... and if i want to find the number of hours simply i will multiply by 24.....
thanks very much
appreciated

 

[jtakw]

You're welcome, thanks for the feedback.

i forgut to say
reason for my thread is that i need to count the number of hours between tow dates....one time including Saturdays and Sundays and the other excluding Saturdays and Sundays

Here are updated formulas for your latest query, Total Hours between Start and End Date (A2 and A3, respectively), then Total Hours Not counting Sat/Sun (also accounting for the Hours of Start/End Dates if they Don't fall on Sat/Sun):

Book3.xlsx
	A	B	C	D
1			Total Hours	Less Sat/Sun
2	01/04/2021 08:15		2192.75	1616.75
3	04/05/2021 17:00
Sheet889

Cell Formulas
Range	Formula
C2	C2	=(A3-A2)*24
D2	D2	=(NETWORKDAYS(A2,A3)+IF(WEEKDAY(A2,2)<6,1-MOD(A2,1),0)+IF(WEEKDAY(A3,2)<6,MOD(A3,1),0))*24

 

[abdi1]

You're welcome, thanks for the feedback.



Here are updated formulas for your latest query, Total Hours between Start and End Date (A2 and A3, respectively), then Total Hours Not counting Sat/Sun (also accounting for the Hours of Start/End Dates if they Don't fall on Sat/Sun):

Book3.xlsx
	A	B	C	D
1			Total Hours	Less Sat/Sun
2	01/04/2021 08:15		2192.75	1616.75
3	04/05/2021 17:00
Sheet889

Cell Formulas
Range	Formula
C2	C2	=(A3-A2)*24
D2	D2	=(NETWORKDAYS(A2,A3)+IF(WEEKDAY(A2,2)<6,1-MOD(A2,1),0)+IF(WEEKDAY(A3,2)<6,MOD(A3,1),0))*24

hi...sorry for late reply
this is another good way to solve the problem and find the number of hours directly
thanks for you jtakw...
appriciated

 

[jtakw]

You're welcome.

When I posted the formulas in Post #8 above, I knew I was forgetting something.
The NETWORKDAYS function is Inclusive of Start and End Dates, which would automatically add 24 hours to each if it's on a Weekday.
So in my formula where I test the Start and End Dates being on a Weekday (Not Sat/Sun), if 1 or both are on a weekday, we also need to subtract 1 day (24 hours) each and add back the Actual hours base on the Time.

So here's the corrected version that should give you accurate results:

Book3.xlsx
	A	B	C	D
1			Total Hours	Less Sat/Sun
2	01/04/2021 08:15		2192.75	1568.75
3	04/05/2021 17:00
Sheet889

Cell Formulas
Range	Formula
C2	C2	=(A3-A2)*24
D2	D2	=(NETWORKDAYS(A2,A3)+IF(WEEKDAY(A2,2)<6,1-MOD(A2,1)-1,0)+IF(WEEKDAY(A3,2)<6,MOD(A3,1)-1,0))*24