Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Saro_2306
Contributor II
Contributor II

Working Hours Calculation

Hi All,

I Need to Calculate Working hours between 2 days. My Working start time is 6pm and Ends on Next day 3am.

Can Anybody help me to get solution. (Working days calculation would be excluding Weekends Sat & Sunday ).

Thanks in advance. 

3 Replies
Or
MVP
MVP

What's your logic for calculating it outside of Qlik?

On paper, it seems like you just need to use networkdays(Start,End)*9 but given that your hours cross midnight it might be a bit trickier and you'd need an if() statement to handle the first and last day's potential rollover hours.

Saro_2306
Contributor II
Contributor II
Author

Hi, 

We Need to calculate TAT for particular process. Our working hours is 6pm to 3am and week off are Saturday and Sunday. if any task started at 11.50pm and ends in 1am my TAT time should be 70mins. I couldn't perform same  in Qlik.   Can you please advice how we need to solve this.

Or
MVP
MVP

That wasn't your original question... you asked how to calculate working hours between two dates.

I don't know if there's any easy or clean way to do it when your hours are spread over two days since they cross the midnight threshold (others might be able to come up with one), see below for links on how you'd do it if the working hours are on the same day. You could have to write a bunch of if statements which align with the logic you want to apply, something along the lines of:

If it's a non-Monday weekday and the hour is 3am or lower, get the time (e.g. 2.5 hours if it's 2:30 am). If it's 6pm or later, get the time and subtract 15 (since 3am to 6pm aren't working hours)

If it's a Saturday and the hour is 3am or lower, get the time (we're still on shift from Friday).

If it's a Monday and the hour is 3am or lower, do  nothing (work doesn't start until 6pm). If it's after 6pm, get the hour and subtract 18 (since work starts 6pm Monday and there was no midnight-3am work).

You can get the hour component by using frac(datetime)*24, and you can still use networkdays() to get the number of days between your two dates, so you just need the if() statements to address the hours component.

 

For other ideas, you can look at:

https://community.qlik.com/t5/QlikView-Documents/Working-Hours-Calculation-excluding-weekends-and-ho...

https://community.qlik.com/t5/QlikView-App-Dev/Networkdays-Calculation-Exact-Decimals-Required/td-p/...

I'm not sure if this will help with your specific requirement owing to the cross-midnight issue, but otherwise it's the same requirement so hopefully it'll help.