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.
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.
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 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().
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
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 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.
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.
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 @Anonymous , who’s been thankfully been working on fixing the date locale issues in the Search Bar and with the now-fixed TEXT() function.
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
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:
