Month end ignoring Saturday and Sunday

[stonecold111]

Hi Qliks,

I have date field which is in mm/DD/yyyy format.

I need month end of that date, for that I used script monthend(date) but my output date should ignore Saturday and Sundays

Example: for this month : 30/9/2018

But want 28/9/2018 as my output date as 30 th is Sunday in this month.

How can I do that?

[tush]

Hi,

please use networkdays()

networkdays (start:date, end_date {, holiday})

Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

Examples:

networkdays ('2007-02-19', '2007-03-01') returns 9

networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26') returns 8

Thanks:-

Tushar

[stonecold111]

Hi Tushar,

I don't want number of days between 2 days.

When I calculate monthend of any date it generally gives monthend of that date even if it sat or Sunday but it should give monthend date ignoring sat and Sunday.

What I want is monthend('18/92018') = 28/9/2018

But it giving 30/9/2018.

[tush · Accepted solution]

Hi Suresh,

     Please try following expression:-

=If(WeekDay(MonthEnd(Shipped))='Sat', Date(Date#(day(MonthEnd(Shipped)-1)&'/'& Month(Shipped)&'/'& Year(Shipped),'dd/MMM/yyyy'),'dd/MM/yyyy'),

If(WeekDay(MonthEnd(Shipped))='Sun',  Date(Date#(day(MonthEnd(Shipped)-2)&'/'& Month(Shipped)&'/'& Year(Shipped),'dd/MMM/yyyy'),'dd/MM/yyyy'),Date(MonthEnd(Shipped)))

)

Please Use your sheet field name for "Shipped" as it is for Date