Hours between two date/time values is an is an important KPI for SLA based industries. We do have a direct function to calculate number of days between two dates excluding weekends which can be done through the COUNT_DAYS( ) function. However, it does not return exact hour difference. You can use the following klip with date/time values from your file to see this data.

This Klip assumes weekly off as Saturday and Sunday.

What the Klip would look like:

Column: Start
It is direct mapping to Start date column of the data source

Column: End
It is direct mapping to End date column of the data source

Data: KStart
It is the new start date for calculation. If Start falls on a weekend. It returns the Monday.

Data: KEnd
It is the new end date for calculation. If End falls on a weekend. It returns the Friday of the previous week.

Data: Exclude weekend
Calculates number of days without weekend.

Data: With weekend
Calculates number of days with weekends.

Column: Hours excluding weekend
Checks if date number of days with weekend is greater than number of days without weekend. If yes, returns hour difference subtracting the 24 hours of the weekend.

You can copy the following Klip source code to a new klip and just map the start and end date to the columns. Make sure you update all the date formats in the Klip to the format you have in your data source:

 Where to copy to:
Klip Editor -> Advanced Tools -> View Klip Source Code -> Paste -> Update

{
 "title": "KF-Calculate Hours excluding weekend",
 "id": "",
 "workspace": {
 "dimensions": {
 "w": 834
 },
 "datasources": []
 },
 "appliedMigrations": {
 "post_dst": true,
 "separate_root_dsts": true,
 "result_rows2": true
 },
 "components": [
 {
 "type": "table",
 "id": "60bb860c",
 "displayName": "Table",
 "dstContext": {
 "id": "dst60bb860c",
 "kid": "",
 "ops": [
 {
 "type": "filter",
 "filterBy": "392f616f",
 "variation": "member",
 "p": {
 "type": "f",
 "f": "not",
 "args": [
 {
 "type": "f",
 "f": "in",
 "args": [
 {
 "dim": "392f616f"
 },
 {
 "type": "f",
 "f": "dst_array",
 "args": [
 {
 "type": "f",
 "f": "blank"
 },
 {
 "value": "\"\""
 }
 ]
 }
 ]
 }
 ]
 }
 }
 ]
 },
 "components": [
 {
 "type": "table_col",
 "role": "tcol",
 "id": "bfeaa309",
 "displayName": "Start",
 "namerenamed": true,
 "size": "xx-small",
 "fmt": "dat2",
 "fmtArgs": {
 "dateInputFormat": "custom",
 "dateInputFormatCustom": "d/M/yyyy HH:mm",
 "dateFormat": "custom",
 "dateFormatCustom": "d/M/yyyy HH:mm",
 "resultMetadata": {
 "ignoreDateFormat": false,
 "isAggregated": false,
 "isDSTApplied": true,
 "resultCount": 1
 }
 },
 "components": [],
 "formulas": [
 {
 "txt": "\"Start Column from your data source\"",
 "ver": 2
 }
 ],
 "data": [
 []
 ],
 "autoFmt": false,
 "name": "Start",
 "index": 0,
 "customHeader": false
 },
 {
 "type": "table_col",
 "role": "tcol",
 "id": "392f616f",
 "displayName": "End",
 "namerenamed": true,
 "size": "xx-small",
 "fmt": "dat2",
 "fmtArgs": {
 "dateInputFormat": "custom",
 "dateInputFormatCustom": "d/M/yyyy HH:mm",
 "dateFormat": "custom",
 "dateFormatCustom": "d/M/yyyy HH:mm",
 "resultMetadata": {
 "ignoreDateFormat": false,
 "isAggregated": false,
 "isDSTApplied": true,
 "resultCount": 1
 }
 },
 "components": [],
 "formulas": [
 {
 "txt": "\"End Column from your data source\"",
 "ver": 2
 }
 ],
 "data": [
 []
 ],
 "autoFmt": false,
 "name": "End",
 "index": 1,
 "customHeader": false
 },
 {
 "type": "table_col",
 "role": "tcol",
 "id": "d692f686",
 "displayName": "Hours excluding weekend",
 "namerenamed": true,
 "size": "xx-small",
 "fmt": "dur",
 "fmtArgs": {
 "resultMetadata": {
 "ignoreDateFormat": false,
 "isAggregated": false,
 "resultCount": 1
 },
 "durationFormat": "hh:mm:ss"
 },
 "components": [],
 "formulas": [
 {
 "txt": "IF(&'102b40f6'>&'77356e04',\n(&'d2b05a71'-&'bcf29997')-(48*60*60),\n(&'d2b05a71'-&'bcf29997')\n)",
 "ver": 2
 }
 ],
 "data": [],
 "autoFmt": false,
 "name": "Hours excluding weekend",
 "index": 2,
 "customHeader": false
 },
 {
 "type": "data_slot",
 "role": "data",
 "id": "bcf29997",
 "displayName": "KStart",
 "fmt": "txt",
 "fmtArgs": {
 "resultMetadata": {
 "ignoreDateFormat": false,
 "isAggregated": false,
 "resultCount": 1
 },
 "autoFmt": {
 "fmtArgs": {},
 "defaultAggregate": "COUNT",
 "fmt": "txt"
 }
 },
 "isDstRoot": false,
 "formulas": [
 {
 "txt": "IF(IN(DATE_CONVERT(&'bfeaa309',\"d/M/yyyy HH:mm\",\"EEE\"),ARRAY(\"Sat\",\"Sun\")),\nDATE_CLOSEST(DATE(&'bfeaa309',\"d/M/yyyy HH:mm\"), \"mon\",\"forward\"),\nDATE(&'bfeaa309',\"d/M/yyyy HH:mm\")\n)",
 "ver": 2
 }
 ],
 "data": [],
 "autoFmt": true,
 "name": "KStart"
 },
 {
 "type": "data_slot",
 "role": "data",
 "id": "d2b05a71",
 "displayName": "KEnd",
 "fmt": "txt",
 "fmtArgs": {
 "resultMetadata": {
 "ignoreDateFormat": false,
 "isAggregated": false,
 "resultCount": 1
 },
 "autoFmt": {
 "fmtArgs": {},
 "defaultAggregate": "COUNT",
 "fmt": "txt"
 }
 },
 "isDstRoot": false,
 "formulas": [
 {
 "txt": "IF(IN(DATE_CONVERT(&'392f616f',\"d/M/yyyy H:mm\",\"EEE\"),ARRAY(\"Sat\",\"Sun\")),\nDATE_CLOSEST(DATE(&'392f616f',\"d/M/yyyy H:mm\"), \"fri\",\"backward\"),\nDATE(&'392f616f',\"d/M/yyyy H:mm\")\n)",
 "ver": 2
 }
 ],
 "data": [],
 "autoFmt": true,
 "name": "KEnd"
 },
 {
 "type": "data_slot",
 "role": "data",
 "id": "77356e04",
 "displayName": "Exclude weekend",
 "fmt": "txt",
 "fmtArgs": {
 "resultMetadata": {
 "ignoreDateFormat": false,
 "isAggregated": false,
 "resultCount": 1
 },
 "autoFmt": {
 "fmtArgs": {},
 "defaultAggregate": "COUNT",
 "fmt": "txt"
 }
 },
 "isDstRoot": false,
 "formulas": [
 {
 "txt": "COUNT_DAYS(&'bcf29997',&'d2b05a71',ARRAY(\"sat\",\"sun\"))",
 "ver": 2
 }
 ],
 "data": [],
 "autoFmt": true,
 "name": "Exclude weekend"
 },
 {
 "type": "data_slot",
 "role": "data",
 "id": "102b40f6",
 "displayName": "With weekend",
 "fmt": "txt",
 "fmtArgs": {
 "resultMetadata": {
 "ignoreDateFormat": false,
 "isAggregated": false,
 "resultCount": 1
 },
 "autoFmt": {
 "fmtArgs": {},
 "defaultAggregate": "COUNT",
 "fmt": "txt"
 }
 },
 "isDstRoot": false,
 "formulas": [
 {
 "txt": "COUNT_DAYS(&'bcf29997',&'d2b05a71')",
 "ver": 2
 }
 ],
 "data": [],
 "autoFmt": true,
 "name": "With weekend"
 }
 ]
 }
 ]
}

- Arshad