question

RP-1627 avatar image
0 Votes"
RP-1627 asked esjimenezp commented

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

office-sharepoint-onlineoffice-sharepoint-server-development
· 15
5 |1600 characters needed characters left characters exceeded

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 ?
I am looking forward to your reply.

Thanks,
Echo Du

0 Votes 0 ·

no not resolved yet

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

0 Votes 0 ·

Hi @RP-1627 ,

I have updated my answer. Hope it will help you.

Have a nice day !

Thanks,
Echo Du

0 Votes 0 ·

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.

I am looking forward to your reply.

Have a nice day!

Thanks,
Echo Du



0 Votes 0 ·

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?

0 Votes 0 ·

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

0 Votes 0 ·
Show more comments

Hi @RP-1627 ,

I have updated my answer in the original My Post.
Hope my answer can help you.

Good luck with your work!

Thanks,
Echo Du

0 Votes 0 ·

Hi @RP-1627 ,

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

I am looking forward to your new reply.

Good luck with your work.

0 Votes 0 ·

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

0 Votes 0 ·

Hi @RP-1627 ,

Thanks your reply. I wish you all the best!

0 Votes 0 ·
Show more comments
EchoDu-MSFT avatar image
1 Vote"
EchoDu-MSFT answered EchoDu-MSFT published

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
39364-days2.png


Thanks,
Echo Du

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

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

39953-1.png

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

40849-datetime.png


Effect picture:

42149-days.png



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.







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)
5 |1600 characters needed characters left characters exceeded

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

RP-1627 avatar image
0 Votes"
RP-1627 answered esjimenezp commented

@EchoDu-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

39742-days.png



days.png (48.8 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

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 ,

I have updated my answer. Hope it will help you.

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


0 Votes 0 ·

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

39907-days.png


0 Votes 0 ·
days.png (43.8 KiB)

Hi @RP-1627 ,

Thanks for your reply and clarification.
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.

Thanks,
Echo Du

0 Votes 0 ·
Show more comments