Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
While Calculating Ageing between two dates can we exclude only specified holidays? (i.e) I need to exclude only the list of dates specified irrespective of saturday or sunday. when I use Network days and specify the list of holidays in a variable. networkdays automatically excldues saturday, sunday also. Pls suggest an alternate approach
HI aswin, =Div(WEEKDAY(day('2017-08-31')-6)+1-date('2017-08-31')+date('2017-09-25'),7)
returns 4, which is correct, it is the no. of saturdays.
If you want to exclude only sundays and holidays, use
Networkdays() = your expression which excludes Sat,Sun,Holiday + my exp which returns no. of Sats
so the above exp will only exclude holidays and sundays. I hope you understand now.
Regards
Pratyush
Add no. of saturdays and sundays in the date range.
Regards
Pratyush
Just add this to your expression
date('2017-09-28')-date('2016-09-28')-NetWorkDays('2016-09-28','2017-09-28')
replace '2017-09-28' with your end date and '2016-09-28') as your start date
Regards
Pratyush
if i need saturdays.. i need to exclude only sunday and public holidays then?
Maybe this,
=Div(WEEKDAY(date('2017-10-07')-6)+1-date('2017-09-22')+date('2017-10-07'),7)
replace '2017-10-07' with your end date and '2016-09-22') as your start date
No this gives wrong number of sundays for many cases. Any other possible way
How do you recognize holidays? is there any flag in your data for holidays?
This is used for Saturdays. You asked me for Saturdays.
Regards
Pratyush
list of Holidays will be maintained in a table or excel file
=Div(WEEKDAY(date('2017-09-25')-6)+1-date('2017-08-31')+date('2017-09-25'),7)
check for these dates.
The Exact Requirment is to Exclude Sundays and Public Holidays..
Eg:
Date 1 : 01/09/2017
Date 2 : 25/09/2017
say 05/09/2017 is only puclic holiday falling under this date range and 3,10,17,24/09/2017 are the sundays in those range
Expected result : 20 Days ( Total 25 days - (4 Sundays + 1 public holiday))