Get the total hours in HH:MM from 2 different time spans.

There was a specific requirement from one of our clients. The requirement was to get the total hours worked in HH:MM from 2 different time spans. The result has to be shown in SSRS.
The expecation was like below.

Day Hours
Night Hour
Total Hours Worked
12:30
27:49
40:19

After detailed research and combination, we had reached the below result.

=
//Converting total Minutes to Hour
Format(
(
//Section 1 Getting Total Minutes of Start time in HH:MM
((Mid(ReportItems!Textbox14.Value,
0 + 1,
(InStr(ReportItems!Textbox14.Value, ":")
) - 1) * 60) +
Mid(ReportItems!Textbox14.Value,
(InStr(ReportItems!Textbox14.Value, ":")
) + 1,
LEN(ReportItems!Textbox14.Value) - 1))

//Summing Total Minutes of start and end time
+

//Section 2 Getting Total Minutes of End time in HH:MM

((Mid(ReportItems!Textbox18.Value,
0 + 1,
(InStr(ReportItems!Textbox18.Value, ":")
) - 1) * 60) +
Mid(ReportItems!Textbox18.Value,
(InStr(ReportItems!Textbox18.Value, ":")
) + 1,
LEN(ReportItems!Textbox18.Value) - 1))

)
\ 60, "0")

& ":" &

//Converting total Minutes to Minutes in the MM section

Format(
(
//Getting Total Minutes of Start time in HH:MM
((Mid(ReportItems!Textbox14.Value,
0 + 1,
(InStr(ReportItems!Textbox14.Value, ":")
) - 1) * 60) +
Mid(ReportItems!Textbox14.Value,
(InStr(ReportItems!Textbox14.Value, ":")
) + 1,
LEN(ReportItems!Textbox14.Value) - 1))

//Summing Total Minutes of start and end time
+

//Getting Total Minutes of End time in HH:MM
((Mid(ReportItems!Textbox18.Value,
0 + 1,
(InStr(ReportItems!Textbox18.Value, ":")
) - 1) * 60) +
Mid(ReportItems!Textbox18.Value,
(InStr(ReportItems!Textbox18.Value, ":")
) + 1,
LEN(ReportItems!Textbox18.Value) - 1))

)
  Mod 60, "00")


I hope that this would help someone who are in similar requirement

Comments

Popular posts from this blog

Authenticating SharePoint with Multi Factor Authentication using PnP PowerShell

Deploying App in SharePoint Online - Sideloading of apps is not enabled on this site.