question

AravindNarasimhan-6882 avatar image
0 Votes"
AravindNarasimhan-6882 asked AravindNarasimhan-6882 commented

Caculated column formula for Networkdays between two dates and time in SharePoint Custom List? To get result in [h]:mm

I have got this code here to get networkdays in SharePoint Custom List.

Now i want the return type (to be displayed) in [h]:mm

=IF(AND((WEEKDAY(Completed,2))<(WEEKDAY(Issued,2)),((WEEKDAY(Issued,2))-(WEEKDAY(Completed,2)))>1),(((DATEDIF(Issued,Completed,"D")+1))-(FLOOR((DATEDIF(Issued,Completed,"D")+1)/7,1)*2)-2),(((DATEDIF(Issued,Completed,"D")+1))-(FLOOR((DATEDIF(Issued,Completed,"D")+1)/7,1)*2)))

@Mchova01

office-sharepoint-onlinesharepoint-workflow
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.

1 Answer

EmilyDu-MSFT avatar image
0 Votes"
EmilyDu-MSFT answered AravindNarasimhan-6882 commented

@AravindNarasimhan-6882

Based on your calculated column, I understand that you want to get workdays between two date and time columns.

Per my test, you could get the total number of workdays or total hours of workdays. But displaying the calculated result as h:mm is not available.

For total number of workdays:
=IF(Completed<Issued,(((DATEDIF(Completed,Issued,"D")))-(FLOOR((DATEDIF(Completed,Issued,"D")+1)/7,1)*2)),(((DATEDIF(Issued,Completed,"D")+1))-(FLOOR((DATEDIF(Issued,Completed,"D")+1)/7,1)*2)))

For total hours of workdays:
=(IF(Completed<Issued,(((DATEDIF(Completed,Issued,"D")))-(FLOOR((DATEDIF(Completed,Issued,"D")+1)/7,1)*2)),(((DATEDIF(Issued,Completed,"D")+1))-(FLOOR((DATEDIF(Issued,Completed,"D")+1)/7,1)*2))))*24


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.




· 1
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.

the above formula works. However, the days get rounded off and not showing fraction for e.g. 1.5 days, 1.7 days etc I have created and completed fields in date/time. So, I would like the formula to consider time as well.

0 Votes 0 ·