Calculating difference between 2 date

R_pat

New Member
Joined
Nov 10, 2020
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
Nov 18, 2019
Messages
9
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
Joined
Nov 10, 2020
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
thank you, but it doesnt work for all my scenarios
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,660
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
How about (please note that my dates are in dd/mm/yyyy format)

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
Joined
Nov 10, 2020
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
i will give this a try tomorrow and let you know
thank you for all your help
 

Watch MrExcel Video

Forum statistics

Threads
1,118,776
Messages
5,574,162
Members
412,574
Latest member
shadowfighter666
Top