# question

## how to calculate number of days between 2 dates with time

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

· 15

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @RP-1627 ,

Would you tell me whether your issue has been resolved or have any update ?

Thanks,
Echo Du

no not resolved yet

i see your response below. thank you i will give it a try and let you know.

Hi @RP-1627 ,

Have a nice day !

Thanks,
Echo Du

Hi @RP-1627 ,

Can you elaborate on the concept of the calculation rules your defined?

Personally, I can’t understand why Text6 counts as 1 day? After all, the time difference is 0.

Have a nice day!

Thanks,
Echo Du

i understand now that the formula is working as it should.
because I am using SharePoint columns, when the user select a date the time is automatically chosen as 12 am if the users doesn't change it to 8am - [enddate] 12pm or 5pm

so by default total days will be 0.

hope that makes sense and you understand what I am trying to achieve.

any ideas no how I can get it to do what I want?

Hello @RP-1627 ,

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 or equal to 4 hrs on the same day is counted as 1 day, if the difference is less than 4 hrs on the same day, it is counted as 0.5 day.

Thanks,
Echo Du

Hi @RP-1627 ,

I have updated my answer in the original My Post.

Thanks,
Echo Du

Hi @RP-1627 ,

Have you tried my latest solution?
Has the problem been resolved or other progress?

sorry no i havent, I will have a try tomorrow and get back to you

Hi @RP-1627 ,

Hello @RP-1627 ,

You could create the **Calculated colum**n 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

Hi @RP-1627 ,

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 @RP-1627 ,

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
==================

days.png (40.6 KiB)
days2.png (11.1 KiB)
days2.png (15.3 KiB)
1.png (12.6 KiB)
datetime.png (86.5 KiB)
days.png (14.6 KiB)
days.png (13.3 KiB)

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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 days.png (48.8 KiB)
· 4

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @RP-1627 ,

You can directly click “Comment” option under ”My Answered“ to put forward your opinions and thoughts about solution that I propose.

Have a nice day !

Thanks,
Echo Du

hi,

the numbers still not working.
to give you a few examples.
test 1: should be 4 days
test 3: should be 3 days
test 4: should be 4 days
test 6: should be 1 day 