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: 
Jason1
Contributor III
Contributor III

Determining if there is a Sat/Sun between two dates

Hi all,

I'm trying to determine whether a Saturday or Sunday can be found between any two datetimes. I have a table with two datetime fields and I have determined the interval between the dates but I would like to ignore Saturdays and Sundays when calculating the interval. Networkdays() isn't accurate enough, I've tried that. If my first datetime is Friday 15th Feb at 1pm and my second datetime is Monday 18th Feb at 1pm, networkdays() returns 2 whole days, when I am looking for an exact interval in days, hours, minutes.

Thanks all!

 

 

1 Solution

Accepted Solutions
Vegar
MVP
MVP

I believe that @Gysbert_Wassenaar solution will work as long as your start time nor end time is on a Saturday or Sunday. 

 

View solution in original post

5 Replies
Vegar
MVP
MVP

A solution to your issue is to split up your transactions into days. Your example transaction, Friday 15th Feb at 1pm to Monday 18th Feb at 1pm, would split into rows like this:
Friday 15th; 11h
Saturday 16th; 24h
Sunday 17th; 24h
Monday 18th; 13h

The benefit is that you will have better control, but you will have to multiply your transaction amount any count transactions expressions will need to be adjusted.
jyothish8807
Master II
Master II

Hi Jason,

Check this, could be helpful:

https://community.qlik.com/t5/QlikView-App-Development/Calculate-hours-between-two-Date-Time-strings...

You have to define the holidays separately if needed.

Best Regards,
KC
Gysbert_Wassenaar

You could calculate the number of saturdays and sundays with this:
Floor(EndData)-Floor(StartDate)+1-NetWorkdays(StartDate, EndDate)

talk is cheap, supply exceeds demand
Vegar
MVP
MVP

I believe that @Gysbert_Wassenaar solution will work as long as your start time nor end time is on a Saturday or Sunday. 

 

Jason1
Contributor III
Contributor III
Author

Thanks for the input everyone, I've marked what worked. Nice and simple, I should have thought of that logic in the first place.