# Calculating difference between 2 date

#### R_pat

##### New Member
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

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Tonmoymridha

##### New Member
you can try this!!!!
Book1
ABCDEF
1Start DateEnd DateMonth DiffHour
211/24/2020 8:0011/27/2020 12:003043 Days 04 Hours
Sheet1
Cell Formulas
RangeFormula
C2C2=DATEDIF(A2,B2,"Md")
D2D2=TEXT(B2-A2,"hhh")
E2E2=CONCATENATE(C2&" "& "Days"&" ",D2&" "&"Hours")

#### R_pat

##### New Member
thank you, but it doesnt work for all my scenarios

#### MARK858

##### MrExcel MVP

Book2
BCD
117/11/2020 08:0017/11/2020 12:000.50
224/11/2020 08:0027/11/2020 14:004.00
324/11/2020 08:0027/11/2020 12:003.50
424/11/2020 08:0027/11/2020 08:003.00
513/11/2020 08:0016/11/2020 12:001.50
Sheet6
Cell Formulas
RangeFormula
D1:D5D1=(NETWORKDAYS(B1,C1)-1)+IF(MOD(C1-B1,1)=0,0,IF(MOD(C1-B1,1)>0.16666667,1,0.5))

#### R_pat

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

Replies
0
Views
60
Replies
13
Views
167
Replies
4
Views
31
Replies
12
Views
196
Replies
5
Views
176