Calculating difference between 2 date

[R_pat]

hello,

I am trying to do this in SharePoint calculated column.

I'm trying to calculate the number of days between 2 dates [Start Date] and [End Date], given 2 dates with time.

I have come across many examples that will calculate the days 90% of the time correctly but it doesnt do half days.

I of course also want to exclude the weekends. and holidays which i have in another list (Something to keep in mind is of course the Year and the Province, different holidays for different provinces)

here are my scenarios:

- 4 hrs is considered half day (0.5 days), anything more then 4 hrs is considered a full day (rounding up). Below should only be 0.5 days
Start Date: 11/17/2020 8am
End Date: 11/17/2020 12pm

- User enter 3.5 days for example, below should be 3.5 days
Start Date: 11/24/2020 8am
End Date: 11/27/2020 12pm

i would really appreciate anyone's help

thank you

RP

 

[Tonmoymridha]

you can try this!!!!

Book1
	A	B	C	D	E	F
1	Start Date	End Date	Month Diff	Hour
2	11/24/2020 8:00	11/27/2020 12:00	3	04	3 Days 04 Hours
Sheet1

Cell Formulas
Range	Formula
C2	C2	=DATEDIF(A2,B2,"Md")
D2	D2	=TEXT(B2-A2,"hhh")
E2	E2	=CONCATENATE(C2&" "& "Days"&" ",D2&" "&"Hours")

 

[R_pat]

thank you, but it doesnt work for all my scenarios

 

[MARK858]

How about (please note that my dates are in dd/mm/yyyy format)

Book2
	B	C	D
1	17/11/2020 08:00	17/11/2020 12:00	0.50
2	24/11/2020 08:00	27/11/2020 14:00	4.00
3	24/11/2020 08:00	27/11/2020 12:00	3.50
4	24/11/2020 08:00	27/11/2020 08:00	3.00
5	13/11/2020 08:00	16/11/2020 12:00	1.50
Sheet6

Cell Formulas
Range	Formula
D1:D5	D1	=(NETWORKDAYS(B1,C1)-1)+IF(MOD(C1-B1,1)=0,0,IF(MOD(C1-B1,1)>0.16666667,1,0.5))

 

[R_pat]

i will give this a try tomorrow and let you know
thank you for all your help