
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Month end ignoring Saturday and Sunday
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?
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
