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

Aravind Narasimhan 1 Reputation point
2021-07-06T12:56:13.683+00:00

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

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,249 questions
SharePoint Workflow
SharePoint Workflow
SharePoint: A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.Workflow: An orchestrated and repeatable pattern of business activity, enabling data transformation, service provision, and information retrieval.
549 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Emily Du-MSFT 44,071 Reputation points Microsoft Vendor
    2021-07-07T10:15:32.493+00:00

    @Aravind Narasimhan

    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.