how to calculate number of days between 2 dates with time

Question

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

Answer 1

Hello @R_Pat ,

You could create the Calculated column in your List to count the number of days excluding weekends:

Go to the specific List >> click on Create Column on the ribbon >> select Calculated type to create a column named "Days“ >> On the Formula section, Please use the following command:

=IF(ISERROR(DATEDIF(StartDate,EndDate,"d")),"",(DATEDIF(StartDate,EndDate,"d"))-INT(DATEDIF(StartDate,EndDate,"d")/7)*2-IF((WEEKDAY(EndDate)-WEEKDAY(StartDate))<0,2,0)-IF(OR(AND(WEEKDAY(EndDate)=7,WEEKDAY(StartDate)=7),AND(WEEKDAY(EndDate)=1,WEEKDAY(StartDate)=1)),1,0)-IF(AND(WEEKDAY(StartDate)=1,(WEEKDAY(EndDate)-WEEKDAY(StartDate))>0),1,0)-IF(AND(NOT(WEEKDAY(StartDate)=7),WEEKDAY(EndDate)=7),1,0)+IF(HOUR(EndDate)<HOUR(StartDate),0,IF(TEXT(EndDate-StartDate,"h")>=4,0.5,1)))  

After performing the above operations, you will find that the SharePoint List will automatically calculate the number of weekdays

Thanks,
Echo Du

…………………………………………………………………… Updated Answer …………………………………………………………………

Hi @R_Pat ,

I have modified Column Formula:

=IF(ISERROR(DATEDIF([Start Date],[End Date],"d")),"",(DATEDIF([Start Date],[End Date],"d"))-INT(DATEDIF([Start Date],[End Date],"d")/7)*2-IF((WEEKDAY([End Date])-WEEKDAY([Start Date]))<0,2,0)-IF(OR(AND(WEEKDAY([End Date])=7,WEEKDAY([Start Date])=7),AND(WEEKDAY([End Date])=1,WEEKDAY([Start Date])=1)),1,0)-IF(AND(WEEKDAY([Start Date])=1,(WEEKDAY([End Date])-WEEKDAY([Start Date]))>0),1,0)-IF(AND(NOT(WEEKDAY([Start Date])=7),WEEKDAY([End Date])=7),1,0)+IF(HOUR([End Date])<=HOUR([Start Date]),0,IF(TEXT([End Date]-[Start Date],"h")>=4,0.5,1)))  

It should be noted that if the [Start Date] is later than the [End Date], the calculation result will not appear. Take Text6 as an example in your screenshot.

Thanks,
Echo Du

……………………………………………………………………… Updated Answer ……………………………………………………………

Hi @R_Pat ,

Does the following statement meet the meaning you want to say ?

When you enter the Date&Time value in the List, if you do not specify a specific time point, SharePoint online will automatically default to 12am.
When you specify a specific time point, if the difference of more than 4 hrs on the same day is counted as 1 day, if the difference is less than or equal to 4 hrs on the same day, it is counted as 0.5 day.

If yes, you can refer to the following formula:

=IF(ISERROR(DATEDIF([Start Date],[End Date],"d")),"",(DATEDIF([Start Date],[End Date],"d"))-INT(DATEDIF([Start Date],[End Date],"d")/7)*2-IF((WEEKDAY([End Date])-WEEKDAY([Start Date]))<0,2,0)-IF(OR(AND(WEEKDAY([End Date])=7,WEEKDAY([Start Date])=7),AND(WEEKDAY([End Date])=1,WEEKDAY([Start Date])=1)),1,0)-IF(AND(WEEKDAY([Start Date])=1,(WEEKDAY([End Date])-WEEKDAY([Start Date]))>0),1,0)-IF(AND(NOT(WEEKDAY([Start Date])=7),WEEKDAY([End Date])=7),1,0)+IF(HOUR([End Date])<=HOUR([Start Date]),0,IF(INT(TEXT([End Date]-[Start Date],"h"))>4,1,0.5)))  

Effect picture:

Thanks,
Echo Du

==================

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

@Echo Du_MSFT

hi,
Thank you for the formula, but there might be a glitch. for some scenarios it works and for some it doesn't. please see image.

also, is there a way to say that if the given request is enter in less then 4hr it should be 0.5 days
and any request over 4hrs on a particular day should be 1full day

hope the above makes sense

Answer 3

Hi @Echo Du_MSFT ,

In my case i have three columns. I would appreciate if i can get a solution.

1. Once an item is created, the count, the time to reslove begins to tick.
2. Once column 1 is fiilled with the date/time, the time to resolve resets back to zero and the date/time beginns to count for column 2
3. Once column 2 is fiilled with the date/time, the time to resolve resets back to zero and the date/time begins to count for column 3
4. Once column 3 has a date, the time to resolve resets to zero.

@Echo Du_MSFT