topic Add Working Days to a date in App Development

Add Working Days to a date

Anonymous — Fri, 03 Nov 2017 10:29:30 GMT

Hi,

Is there any function to add working days?

The function should do the same as Excel function Workday(date, workingDaysToAdd)

I tried lastworkdate() function, but the function doesn't return the same results as Excel Workday()

qlik: lastworkdate(date, 5)

excel: workday(date, 5)

date	qlik	excel
25-Mar-17	31-Mar-17	31-Mar-17
26-Mar-17	31-Mar-17	31-Mar-17
27-Mar-17	31-Mar-17	3-Apr-17

Re: Add Working Days to a date

prat1507 — Fri, 03 Nov 2017 10:40:06 GMT

Maybe this

If(Weekday(date)=Sun or Weekday(date)=Sat, lastworkdate(date, 5), lastworkdate(date, 5+1))

Regards

Pratyush

Re: Add Working Days to a date

techvarun — Fri, 03 Nov 2017 11:23:49 GMT

=WORKDAY(Date,5) - Will not consider the date and show fifth working day from the Date

=lastworkdate (Date, 5) = Will check the date is Holiday or not first if yes it will consider that also and show the fifth date

Re: Add Working Days to a date

sunny_talwar — Fri, 03 Nov 2017 11:37:13 GMT

Seems like Qlik is including 27th, where as Excel might be starting the count from 28th... it's just the matter of what you want... may be all you want is this

LastWorkDay(date+1, 5)

Re: Add Working Days to a date

Anonymous — Fri, 03 Nov 2017 14:32:02 GMT

Thanks, it works.

I realized it works only for positive numbers.

Any solution for negative numbers? I mean something like lastworkday(date, -5)

Re: Add Working Days to a date

Anonymous — Fri, 03 Nov 2017 14:35:31 GMT

Thanks, btw it seems you missed single quotes signs.

if(Weekday(date)='Sun' or Weekday(date)='Sat', lastworkdate(date, 5), lastworkdate(date, 5+1))

btw, Instead of Weekday(date)='Sun' it's more safely to use Weekday(date)=6

Re: Add Working Days to a date

sunny_talwar — Fri, 03 Nov 2017 14:43:43 GMT

Check if FirstWorkDay() works for that...

FirstWorkDay(date, 5)

Re: Add Working Days to a date

Anonymous — Wed, 08 Nov 2017 09:42:14 GMT

It seems that for negative numbers we have to use

FirstWorkDay(date-1, 5)

That's really bad we have to use two different functions for (basically) the same.

Excel is better again.