Networkdays

[MEC]

Hi Appsheet,

Just wanna ask if you have already come up with a built-in formula of NETWORKDAYS()? My problem basically is that, I need to count the no. of days excluding weekends and holidays from a given starting date up to now() then return that number.

Thanks in advance!

Regards,

[AlexM]

Add a count column for worked day and just do last - the one you want from

[Aleksi]

Not very elegant but it should work…
2*HOUR([DATE]-TODAY())/24-
HOUR(WORKDAY(TODAY(),HOUR([DATE]-TODAY())/24)-TODAY())/24

[MEC]

Thank you Aleksi!

[OptimiX_XcrY]

I think this formula generates wrong result for time intervals bigger than a week or so , this being because the number of weekend days until the workday result can be bigger than the double number of weekend days from the original time interval.

I could also be wrong and just gone crazy searching for a solution for my situation.

[BlakeHammond]

I know this is an old reply. But hopefully, you can still help.

I have modified your formula slightly for what i need

2*HOUR([End DATE]-[Start Date])/24-
HOUR(WORKDAY([Start Date],HOUR([end DATE]-[Start Date])/24)-[Start Date])/24)

The issue I have is

if [start date] is 26/03/2021 [end date] is 29/03/2021 it comes back with 1 not 2 as 26th and 29th need to be included.

also if [Start date] is 26/03/2021 [end date] is 02/04/2021 it comes back with 4 not 6.

any ideas

[Murat_Turkcelik]

Hi,

I tried another approach and it seems it works. I added “1” at first because I wanted to calculate total work and work starts from the beginning of the start date takes till the end of the finish date.

I assumed Saturday and Sunday are always standard weekend, that’s why I multipled total number of weeks between two dates by 2. As an alternative, it can be set as a variable. In my case, it was not needed.

The last row counts other days off excluding weekends. I have such a table named “Off Days” for that.

(1+
HOUR((TODAY()-[Start Date]))/24
-(
((YEAR(TODAY())-YEAR([Start Date]))*52
+WEEKNUM(TODAY())-WEEKNUM([Start Date]))*2+
COUNT(SELECT(Off Days[Date],AND([Date]>=[Start Date],[Date]<=TODAY()),TRUE))
)

Hope it helps

Regards

[sathishk]

thanks a lot for the solution

[Aleksi]

You’re welcome

[OptimiX_XcrY]

I am trying to count the working days between two dates , except custom holidays. In Gsheet i did it with a column =ArrayFormula(IF(LEN(B2:B);networkdays(C2:C;D2:D;‘Libera Nat’!B2:B);"")) , “Libera Nat” being a custom table for holidays.

To speed and fine tune my app , i am trying to eliminate all Gsheet formulas , and i don’t know if this one can be moved in appsheet formulas in an elegant way.

Can my Gsheet formula be addapted to an Appsheet formula?

Thank you

[Steve]

This very topic appears to address your question.

[OptimiX_XcrY]

You are right , I didn’t read enough

WORKDAY( when , days [ , holidays ] )

[OptimiX_XcrY]

I am sorry , I am still struggling

1. In your formula , [date] was considered in the past or in the future ?
2. How do I insert a holidays table in the formula, taking into consideration that some holidays can also be in weekends (to not double count those day as weekend days and as holidays too)
3. Is your formula build to count only between dates, and not checking and counting the start and the end date ?

[OptimiX_XcrY]

This is the networkdays equivalent formula for my case presented in previous messages :

1. Startdate and Enddate are in the same year ( I think my column names equivalent in the formula are clear enough)
2. Startdate and Enddate are checked and counted too , as workdays or weekend days
3. Holiday table is a 2 column table ([ziua cal] as date column , and a [datename] column , table name “Libera nat” ) . If a holiday day is a weekend day too , is not substracted twice.

HOUR([DataConcFinal]-[DataConcStart])/24 +1 -
(
2*(weeknum([DataConcFinal])-weeknum([DataConcStart])+1) -
(ifs(weekday([DataConcStart])<>1;1)+
ifs(weekday([DataConcFinal])<>7;1))
)
-count(select(libera nat[ziua cal];and([ziua cal]>=[DataConcStart];[ziua cal]<=[DataConcFinal];weekday([ziua cal])<>1;weekday([ziua cal])<>7);true))

All my test were correct so far, and if someone sees something wrong , please tell me. I will use this as app formula to write in tables for vacation days forms.

[Kanjira_Songpai]

hi i have use this function to get the number of workdays in a period of time but i need more detail such as:
start time of the start date is 2 pm.
end time of the final date is 10 am.

i need to calculate the total minuites of the workdays period. how can i do?