Working days

[Fahad]

Hey guys,

I have an Issue where I need to calculate and assign a flag the working days time minus the weekend time to get the exact working days but It's not working  I would really appreciate your Input In It.

here Is what I have:

If(num(Today()-StartProjectDate) - num(Today()-StartProjectDate and IsWorkingDay=0) <1,'1',
If(num(Today()-StartProjectDate) - num(Today()-StartProjectDate and IsWorkingDay=0) <=3,'2',
If(num(Today()-StartProjectDate) - num(Today()-StartProjectDate and IsWorkingDay=0) >=4,'3','4'))) as NumberOfDaysOpenFlag

IsWorkingDay=0 Is a flag Indicating the weekends.

[Vegar]

You could try to use networkdays() to solve this. 

networkdays (StartProjectDate, today()-1) as NumberOfDaysOpenFlag

The networkdays() automatically exclude weekends from the calculation.  It is also possible to add more custom non working days in a third parameter if needed.

[stevejoyce]

I would think your trying to do (Today() - StartProjectDate - If(IsWorkingDay = 0,1))

Not sure how your dataset is structured.  It seems like StartProjectDate is a single record per project and IsWorkingDate is a daily granularity, but that should give the number of IsWorkingDay = 1 from today - startproject.

[Fahad]

Thanks For the help All

Mr.Vegar

Some of the projects starts in the weekend so i don't want to exclude the weekend i just want to exclude it's time So dose the Function Only exclude the time and shows the record Thanks And I appreciate your support and waiting for your replay.

Mr.Steve 

am not sure what the output of the Calculation you provided would give but for the date field I have it represent the start of the project as a datetime and I want to count the days the project been opened without counting the weekend time ,Note that some of the projects start in the weekend.

[Kushal_Chawda]

@Fahad  what do you mean by excluding time? Could you share a example with expected output?

[Fahad]

Hey Kushal,

So for example a project start In the weekend  and It's been opened for 5 days since the start I don't want It to show 5 days It should be 3 days not counting the weekend, my fear Is If I exclude the weekend the projects that have been opened In the weekend will be excluded as well.

[Vegar · Accepted solution]

I think you missunderstood the networkingdays function.

Take a look at this script.

SET DateFormat='YYYY-MM-DD';
LOAD *,
WeekDay(StartProjectDate) as ProjectStartWeekday,
today()-StartProjectDate as DaysSinceProjectStart,
NetWorkDays(StartProjectDate, today()-1) as NumberOfDaysOpenFlag //Is this what you are trying to do?
inline [
  ProjectName, StartProjectDate,
  Alfa, 2021-08-24
  Bravo,2021-08-25
  Charlie, 2021-08-26
  Delta, 2021-08-27
  Echo, 2021-08-28
  Foxtrot, 2021-08-29
  Golf, 2021-08-30
];

It will be able to give you this output data.

[Fahad]

Yes Thank you very Much for your support