Support Google Sheet formulas under Spreadsheet formula

[Chern]

I am building a HR application with a feature to allow users to apply for leaves.

As I can't find any compatible features under appsheet's app formulas, I used google sheet's Networkdays.Intl() to build my logic.

I noticed the formulas are not supported when I make entries through the app as it generates a formula with "_xlfn." prefix

"_xlfn.NETWORKDAYS.INTL(H13,L13,E13)-_xlfn.NETWORKDAYS.INTL(H13,L13,E13,PublicHoliday)+_xlfn.NETWORKDAYS.INTL(H13,L13,E13)-NETWORKDAYS(H13,L13)"

[Joseph_Seddik]

Welcome to the community !

See this:

https://help.appsheet.com/en/articles/2357330-workday 

[Chern]

@Joseph_Seddik I was trying to calculate the number of days between two dates excluding offdays and holidays. But I have users on different offdays

I.e. Some users are off work on Fridays & Saturday, others are off on Saturdays & Sundays

I think the workday formula is slightly different in its intended calculations.

[Joseph_Seddik]

I see. That would be a bit complicated but nevertheless doable. 

Set your column type in AppSheet as Number, and use the expression below as its formula. This assumes that:

• you have a table "Holidays" listing public holidays
• you have a column "weekend" that indicates whether the weekend for an employee is "SatSun" or "FriSat"

FLOOR( DECIMAL( INDEX( SPLIT([endDate] - [startDate], ":"), 1) ) )
- (
  FLOOR( DECIMAL( INDEX( SPLIT([endDate] - [startDate], ":"), 1) ) ) / 7 * 2 
  + 
  IF( NOT( MOD(
    FLOOR( DECIMAL( INDEX( SPLIT([endDate] - [startDate], ":"), 1) ) ), 7) = 0),
    SWITCH([weekend],
      "SatSun",
        IFS(
          WEEKDAY([startDate]) = 7, 1,
          WEEKDAY([endDate])   = 7, 1,
          WEEKDAY([endDate])   = 1, 2,
          TRUE, 0
        ),
      "FriSat",
        IFS(
          WEEKDAY([startDate]) = 6, 1,
          WEEKDAY([endDate])   = 6, 1,
          WEEKDAY([endDate])   = 7, 2,
          TRUE, 0
        ),
      0
    ),
    0
  )
  +
  COUNT( SELECT(Holidays[date], AND(
    [date]  > [_ThisRow].[startDate],
    [date] <= [_ThisRow].[endDate],
    SWITCH([weekend],
      "SatSun", NOT( IN(WEEKDAY([date]), LIST(1, 7)) ),
      "FriSat", NOT( IN(WEEKDAY([date]), LIST(6, 7)) ),
      TRUE
    )
  )))
)

[SylvainB]

same problem : I want to use =NB.JOURS.OUVRES.INTL(J10; P10), but AppSheet add the formula as =_xlfn.NETWORKDAYS.INTL(J10; P10)... that doesn't works !