Target Workday Calculator

[Joseph_Seddik]

Hello, 

I made this calculator to answer a requirement in an app of mine. I'd like to share it since I think it could be useful for your apps. 

What it does:

• From a starting date, calculate the target date for a task or a project, providing a given number of workdays.
• Let you define an arbitrary work week. You can select whatever weekdays and toggle each of them as a workday or a day off. 
• Let you select the holiday dates. 
• Both weekdays and holidays can be selected before hand so that they serve as initial values for calculation records. Also, each calculation record can have its own set of workdays and holidays. 
• Let you choose whether to include the start date in the calculation.
• You can easily integrate the app views or just copy the formulas to your app. Or use it as a stand alone calculator. 
• The app does not use actions, or virtual columns, just simple formulas. 
  
  

Result:

• The Target Date on which the task will end.
• A list of All Working Dates from start to finish. 
  
  

Requirements: 

• A single-column table, listing dates.
  The sample app already include dates ranging from 1-1-2022 till 5-1-2071. 
  
  

Here it is. Comments welcome. Thanks.

Target Workday Calculator 2 

[Aurelien]

kudos !!!!

[Suvrutt_Gurjar]

Nice one @Joseph_Seddik .I quickly tested as a user and it looks elegant. 

Will try to understand functionality in deeper after translating Spanish column names.  

[Joseph_Seddik]

@Suvrutt_Gurjar Thanks my friend!

I'm sorry for this I'll have to find time to change all formulas. But you don't have to translate; you can just relate using the English Display Names for columns and view, the whole interface is in English. In any case, tell me please if something is not clear, or perhaps I should do a video. Thank you.

[SkrOYC]

It's really good @Joseph_Seddik

It seems like the key is the Date table.

The column names may be in english for future sample apps in order to help it's understanding. Eventhough the Display name is on english, expressions use the actual name, so it's a little difficult to understand what's going on under the hood.

Apart from that, it's perfect

BTW, this may be doing what WORKDAY() does but with two different points:

Workday doesn't need a table with the dates.

Your method allows custom workdays, where WORKDAY() is just monday->friday

I'm right?

Thanks again, nice demo!

[Joseph_Seddik]

@SkrOYC Thanks my friend 🙏

I’m sorry this is taken from a French app and I thought changing the display names to English would be enough to make it understandable. I will change the column names and formulas accordingly. Thank you. Update: sorry this has proven to be too time consuming. Please tell me should you have difficulties and I'll be happy to assist; there's basically only one formula in the app.  

Regarding the dates table, due to lack of iteration expressions in AppSheet, I had to make a choice between:

•  generating the dates list on the fly using either:
  - recurrent behavior actions,
  - calling js libraries from the sheet. 
  
  AppSheet’s WORKDAY() is probably using a C* library for such on-the-fly generation of dates, and this library itself possibly contains a static list of dates, topping at year 9999. 
  
  
• or just having a static date list ready, in a table. 

I opted for the latter because it makes calculations immediate and is just simpler. 

And you are right, this calculator is a solution for the inability to define arbitrary week workdays in WORKDAY(). 

[SkrOYC]

I have a situation where WORKDAY()+holidays is enough but I didn't though of the usage of a full table for a custom workday() kind of workaround, so it's a good idea I will apply in the future.

Also, Date tables are extremely helpfull for a lot of other stuff.

You can use them where the date is the key and you can then reference them on any other table were there is a date to get grouping for example. Since each date has it's own month, week, weekday, and a combination of them (like this "2022-03-MARCH-W04") you could make those on the Date table and then just reference from the other ones

[Gustavo_Eduardo]

Hi oscar, I want to tell you about an experience we had with @Joseph_Seddik , which is that sometimes there is a calculation error by appsheet (let's say it that way). In other words, once all the data has been entered into the app, one would expect it to take the dates as requested, however, due to a format error in the dates or perhaps problems with the appsheet servers, the calculations are not always accurate. This leads me to not trust the solution, not because Joseph's solution isn't good, on the contrary it's genius, but because appsheet sometimes crashes. Imagine making a paid app that then returns a crash. That wouldn't be too serious. So I opted to wait for the solution that Appsheet will one day give us, at least until this error stops appearing. I hope Appsheet reads this

[SkrOYC]

Thanks Gustavo for the insight.

It would be very useful to have more info about the bug so that we can help and also there is feedback to the AppSheet team to solve it

[Gustavo_Eduardo]

@SkrOYC I don't know exactly what the error is because to tell the truth I am the example of a person without knowledge creating an application based on pure perspiration. However we were discussing the issue with @jose_seddik and he was always altruistic about it. He gave the solution to my doubt and created the Workday Calculator, however when I implemented it, there were times that I took the end date calculation well and other times that I directly skipped it and took dates years later. At first I interpreted that it would be a matter of format since in the US the dates are placed on 6/2/2022 while in Latin America it is 2/6/2022. I corrected the format in google drive so that they are dates with origin in Argentina, I also did it from the app editor and I assumed that this would be corrected. Then Joseph saw it and was constantly getting errors in the editor. Sometimes it worked and sometimes it didn't, especially it broke when I updated a record (let's say I incorrectly marked something and went to edit) once I saved and the date had been unpacked.

What Joseph deduces is that the problem is that Appsheet works with multiple servers and it works fine on some but when I have the misfortune to connect to another server the app breaks. Let's say you misread date lists and holiday listings.

Finally, I think Appsheet should add a parameter to Workday as many of us need it because it's so useful.

We all work for productivity.

------

En español

@SkrOYC Yo no se exactamente cual es el error porque a decir verdad yo soy el ejemplo de una persona sin conocimientos creando una app a base de pura transpiración. SIn embargo estuvimos discutiendo el tema con @Joseph_Seddik y el siempre fue altruista a respecto. El le dio la solución a mi duda y creo el Workday Calculator sin emabargo cuando yo la implementé, había veces que me tomaba bien el cálculo de fecha de finalización y otras veces que directamente lo salteaba y tomaba fechas años posteriores. Yo interpreté al principio que se trataría de una cuestión de formato ya que en EEUU las fechas se colocan 6/2/2022 mientras que en latinoamérica es 2/6/2022. Corregí el formato en google drive para que sean fechas con origen en argentina, también lo hice desde el editor de la app  y supuse que así se corregiría. Después lo vio Joseph y le dieron constantemente errores en el editor. A veces funcionaba y a veces no, sobre todo se rompía cuando actualizaba un registro (supongamos que haya marcado erróneamente algo y entro a editar) una vez aue guardaba ya la fecha se había descompaginado.

Lo que Joseph deduce es que el problema es que Appsheet trabaja con varios servidores y que funciona bien en algunos pero que cuando tengo la mala suerte de conectarme con otro servidor la app se rompe. Digamos que lee mal las listas de fechas y las enumlist de holydays.

En fin, creo que Appsheet debería agregar un parámetro a Workday porque ya somos muchos los que lo necesitamos y es muy útil. 

Todos trabajamos por productividad.

[Joseph_Seddik]

The problem as I saw it is in the app preview within the editor. This preview is no where reliable as for correctly interpreting locales when dealing with dates, it literally behaves at will ! Sometimes it does interpret the dates correctly and sometimes it does not. 

This is another post describing the same issue:

Date showing in another format - Google Cloud Community  

Similarly to this post, I personally haven't been able to reproduce the problem outside the editor. 

[SkrOYC]

So it's not a big problem, is it?

I mean, I don't trust the editor for anything but UI changes

[Joseph_Seddik]

It would be a big problem if it manifests in the user's app view. But, personally, I see it only in the editor. 

I'm mentioning @Former Community Member, who's been thankfully been working on fixing the date locale issues in the Search Bar and with the now-fixed TEXT() function.

[Gustavo_Eduardo]

Intentaré reincorporar a la app dicha función y cambiaré de navegador a chromium que según tengo entendido anda mejor. De todos modos si funciona bien en la app, el editor no me importará ya entonces. Te cuento cómo avanza Joseph

[Gustavo_Eduardo]

Hi Joseph!! Good day!! I wanted to ask you about this app "Workday Calculator".

In the "Work week" view I can preselect the work week. So far everything perfect.

When I do it with my user I select Monday, Wednesday and Friday.

If I change User, I choose Tuesday, Thursday and Saturday.

When I go back to my user, it is preloaded as Tuesday, Thursday and Saturday but I had set it to Monday, Wednesday and Friday.

The question is... is there a way to not alter the user choices?

A hug friend thanks for your answer in advance.

[Joseph_Seddik]

Hello dear Gus !!

The Calculator form uses the Week table just to set an initial value of the working weekdays column and each Calculator record has its own value of this column. The Week table itself has no distinction of users, but it serves no other purpose than setting the initial value in the form. 

If you want to have a per-user initial-value memory for the calculator form, then I'd go with USERSETTINGS. You can have a workingWeekDay column for example, type EnumList, base type Ref to the Week table. 

Users will be able to set the week workingdays through the Settings menu. While in the Calculator formula, you should put the Initial Value of the working weekdays column to: 

[_thisUser].[workingWeekDays]

That should give you the desired behavior. 

[Gustavo_Eduardo]

Muchas Gracias Joseph! un abrazo amigo, mañana al levantarme lo pruebo!

[abood89]

Great work thanks for sharing

Noob question

What does  "if ( [ouvrable],False,True) " means ?

I know that IF works with condition but how exactly this works If([ouvrable])?

[Aurelien]

Hi @abood89 

"ouvrable" means "workday" in French.

By the way, you may want to replace this expression with:

NOT([ouvrable])

[abood89]

Thanks for replay 

What I meant is what does the expression IF ([ouvrable]) mean ? How does that expression works ?

I know you could compare it to other value , but if I keep it as

IF ([ouvrable]) what does that mean ?

[Aurelien]

There is no 

IF ([ouvrable])

Just:

NOT([ouvrable])

That means :

- If [Ouvrable] is true, you will get False.

- If [Ouvrable] is false, you will get True.

For reference:

IF() 

[abood89]

Thanks 

if ( [ouvrable],False,True)

You can find it in actions ,it is there 

[Joseph_Seddik]

As @Aurelien said, this is: NOT([Ouvrable]) 

There are three actions. Two of them have NOT([Ouvrable]) and one had If([Ouvrable], FALSE, TRUE) which is the same. I put the lattere since as far as I could remember I was demonstrating something to someone.

[abood89]

Thats why I got confused at first place however,now  I can see your point 

[Joseph_Seddik]

@abood89 You have to understand the notions of arguments and return values.

Arguments are the inputs provided to a function. For example, IF() requires three arguments: a condition, a value if the condition is true, and another if it is false.

The return value is the output of a function. You can think of it as the value that will replace the function after it has been executed. For example: TODAY() which is a function that takes no arguments, when executed at the time I'm writing this comment will return 28/06/2022. AppSheet's interpreter will remove TODAY() and put in its place the value "28/06/2022". 

Both, arguments and the return value, will eventually be replaced by the corresponding value calculated/retrieved by the function. Each value has a data type, these are the same types you define for your columns. For example, the first IF() argument should be a Yes/No (boolean) value, either provided directly, or returned by a function; while the value returned by TODAY() is of Date type. 

Now back to your question, this is the syntax of IF() as explained in the reference document:

IF(is-true?, then-do-this, else-do-this)

IF() expects the first argument to be a Yes/No value. This value can either be:

1. returned by a function, like: ISBLANK([someColumn])
2. returned by an expression, like: COUNT([someList]) > 10
3. retrieved from a column value, like: [trueOrFalseValue]
4. or provided directly, like: TRUE or FALSE

The answer of your question is case no. 3. 

[abood89]

Thanks man that answered my question 

[Gustavo_Eduardo]

Hello Joseph, good day. I've been trying to recover the work I did some time ago. I had to redo it because of the amount of rookie mistakes (as they say here in Argentina, "No hay mal que por bien no venga" or congratulations).

I made some modifications to the work.

1. I made Google Sheet have the same localization as AppSheet.
2. I formatted the "Date" column.
3. I deleted all the dates entered in Google Sheet.
4. I entered two consecutive dates from AppSheet to the Google Sheet.
5. In Google Sheet, I filled the entire sheet downwards, shifting the consecutive cells.
6. The EnumList field in AppSheet had a comma (,) as a date separator. I removed it and let the system choose the separator.
7. I have also added the expression SORT(datesList,false) so that they are organized in descending order since I have noticed that they are often disordered.

It works from both mobile and browser, and apparently it has also been corrected in the editor.

Another thing I noticed is that I had many incorrectly calculated records, and (logically) when updating any field in the table, all the formulas are recalculated and therefore the dates as well.

I hope this change serves you in some way. Regards.

[Joseph_Seddik]

Thank you @Gustavo_Eduardo !