Networkdays

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,

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

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

5 Likes

Thank you Aleksi!

You’re welcome

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

This very topic appears to address your question.

1 Like

You are right , I didn’t read enough :frowning:

WORKDAY( when , days [ , holidays ] )

1 Like

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 ?

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.

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.

1 Like

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