Counting workdays

[Jean-Pierre Hermans]

How can I count the working days between 2 dates?
So no Saturdays and sundays included. Need also formula to count the fridays between 2 dates.
Thank you..

[Brendan]

I have a function called DAYS(Start Date, End Date), but it doesn’t ignore weekends.

And I don’t have a function to count specific days within the week across two dates.

Sorry about that.

Brendan

[Jean-Pierre Hermans]

No problem.
Thanks anyway.

[Jose Monteiro]

Hi Jean-Pierre,

I’ve been trying to find a solution for your problem but unfortunately TF does not have a Conditional Function needed for that purpose.

It is a function like:
IF(X;Y;Z;T)
If X<0 return Y;If X=0 return Z;If X>0 return T

Anyway if you work with long periods there is a small procedure that can help you count working days between dates.
It’s a bit tricky but works.
You must be aware that in the function DAYS(X;Y), the start date X, and the end date Y are not in the range you are counting working days.

So, suppose you are counting work days between 31/12/2016 and 15/11/2017 (319 days).
You could do the following:

1. Have a date column with the Start Date (SDT)
2. Have a date column with the End Date (EDT)
3. Have a calculation column NDAYS with DAYS(SDT;EDT), which tells you the number of days between dates. (319)
4. Have a calculation column with WEEKS with TRUNC(NDAYS/7); that tells you the number of whole weeks between the two dates. (45)
5. Have a calculation column with the reminder days between NDAYS and (WEEKS * 7).
6. That would be RDAYS and the formula (NDAYS-TRUNC(NDAYS)/7)*7
7. This column gives you a number between 0 and 6 that are the number of days left between the Total Days in the range and the Total Days in Whole weeks. (4)
8.Now have a calculation column to count the working days in whole weeks, WKDAYS_WEEK with the formula WEEKS * 5; (45*5=225)
9. Now have a number column, WKDAYS_REM, to keep the number of working days within RDAYS.
10. Say RDAYS has a value of 4.
11. You should look at the first 4 days in the period, count the number of working days and type that value in WKDAYS_REM. (3)
12. Then a Calculation column adding WKDAYS_WEEK and WKDAYS_REM will give you what you want, the number o working days between two dates. (225+3=228)

You can use the same principle to count the number of Fridays or any day of the week between two dates.

Hope this helps.

I have a small Form where I did my tests.
If you want I can send it to you.

Jose

[Jean-Pierre Hermans]

Hello Jose
If it is possible to send the form to me it would be a great help. Thank you for this.

[Jose Monteiro]

Hi Jean_Pierre,

The file is the attached one.

I’m sorry, but on my previous post, the formula on point 6. is not correct; but on the attached file everything is ok.

Then:
1.We are looking for work days in the range [01-01-2017; 14-11-2017].
2. On column STD we type the date before day 1, 31-12-2016; it is the enclosing starting date.
3. The column DW_STD is a calculation column just to tell us the day of the week of column STD.
4. On column ETD we type the date after the last day, 15-11-2017; it is the enclosing ending date.
5. Column NDAYS tells us the number of days in the range on point 1. above.
6. Column WEEKS tells us the number of whole weeks in the range on point 1. above.
7. Column RDAYS tells us the number of days left not included in whole weeks.
8. Column WKDAYS_WEEK tells us the number of work days in whole weeks.
9.Column WKDAYS_RM tells us the number of work days in the remainder 4 days (column RDAYS).
9.1. We have to count these days and type them in WKDAYS_RM.
9.2. So, as 31-12-2106 is Saturday, the first day in the range is Sunday, a NWD (non working day).
9.3. The following 3 days are Working days, Monday, Tuesday, Wednesday.
9.4. We type 3 on the Column WKDAYS_RM.
10. Finally Column ALL_DAYS adds WKDAYS_WEEK and WKDAYS_RM and shows all working days in the range.

Jose

Attachments:

You must be logged in to view attached files.

[Jose Monteiro]

Hi Jean-Pierre,

News about the form for calculating working days between dates.

1. BUG
There is a Bug in the form I sent you.

2. IF(X;Y;Z) function
Brendan explained me in detail how the function IF(X;Y;Z) works so I found a way to do all the calculations automatically.

3. NEW FORM
I have a new form where you just need to enter STD and EDT and you get the Working Days between these two dates automatically.

4. DAYS BETWEEN DATES
There must be a bug in the formula that calculates days between dates DAYS(STD;EDT);
I’ll have to ask Brendan about that.
So it is not worth sending you this new form now.

I’ll tell you more as soon as I understand the problem with the function DAYS(STD;EDT).

Jose

[Jean-Pierre Hermans]

I have also seen that there is something wrong in the form. Was also looking what’s wrong but cannot see it.
Thanks anyway.

[Brendan]

I’m looking into the issue with the DAYS() function now. I assumed the operating system would take care of it properly, but it seems to be considering the time portion of the dates in the calculation, so it’s returning different values for the same 2 dates when they have different times.

[Jose Monteiro]

Hi Jean-Pierre,

May I ask you why do you need to count Fridays between dates?

Jose

[Jean-Pierre Hermans]

Hello Jose, I want to make a form to count working hours on a assignment. On Friday I work 7 hours and the other days 8. That is the only reason.

[Brendan]

I am working on an update which corrects the DAYS() function issue Jose brought to my attention. Plus I’ve added a WEEKDAYS() function to count the number of weekdays between two dates. I’m not sure the best approach to writing a function to return how many Fridays there are between two dates though. Maybe something like DAYSFORDAY(Day, Start, End) where Day is a number from 1 to 7 to signify which day of the week you want to count. Just an idea. I thought there might be something in Numbers or Excel to do this, but I don’t see that function in there.

Do you have an Excel or Numbers formula that does this for you already?

[Jose Monteiro]

Hi Brendan,

The way you put it is exactly my idea.
I would suggest another name for the function:
DAY_IN_DAYS or DAYINDAYS (Day, Start, End)

Jose

[Jean-Pierre Hermans]

Brendan,Jose looks indeed nice if such a function could be implemented. I am sure that more users will love this. Thanks.

[Brendan]

Ya, I thought about a DAYSFORDAY(Start, End, DayNum). But I believe everyone has a different idea of what the day numbers should be. E.g. Is Sunday Day 1 or is Monday Day 1? For me, Day 1 would be Monday. But in Europe I believe it would be Sunday. But I believe there’s functions in macOS and iOS that will tell me what to use for that. So if someone in Europe specified Day 1, they would get a count of the Sundays between the start and end dates. If someone in North America specified Day 1, they would get a count of the Mondays between the start and end dates.

[Jean-Pierre Hermans]

Hi Brendan, I am from Europe (Belgium) and here working days is Monday and this is 1 and Sunday is 7.

[Jose Monteiro]

Bonjour Jean-Pierre,

Ici Jose à Lisbonne, au Portugal. :)

I’ll write in english so everyone can read it. :)

Hi Brendan,

The name of the function is, as you say, a matter of taste, so use the one that sounds better to you.

About formatting dates and numbering week days I recall a document you sent me and helped me a lot.

It’s

Unicode Technical Standard #35
UNICODE LOCALE DATA MARKUP LANGUAGE (LDML)
PART 4: DATES

http://www.unicode.org/reports/tr35/tr35-31/tr35-dates.html#Date_Format_Patterns

There you can find number 1 corresponds to Monday and 7 to Sunday.

Using your DATE() function for days numbering DATE(SomeDate,”e”) we get 1 for Monday.
Result Type must be Text Mode in the formula window.

I believe the standard should be followed.

Jose

[Jean-Pierre Hermans]

Hi Brendan,
Wil the calculating workdays be implemented in a next version? If needed I can test this function before release. Thank you.

[Brendan]

Hi Jean-Pierre,

Yes, the next update will have it.

I’ll be releasing it this coming week to Apple for review.

I can add you to Apple’s TestFlight system if you want so you can download the beta version. It replaces the version you have already. And you’ll be notified when I release new betas.

[Brendan]

Oh, let me know which email address you would like me to use. If it’s the one associated with the forum, just let me know that and I’ll look it up so you don’t have to type it here.

[Jean-Pierre Hermans]

Hi Brendan, email is the one registered here on the forum. Thank,you.

[Chris Knight]

Hello Jean-Pierre,

I’ve been thinking about this ever since you originally posted. I’ve been wanting a DAYSFOR DAY() function like was discussed here but it has not happened yet and no word if it is even on the to-do list. Brendan has a lot on his plate. I see that you have been given access to easy releases so I think I can help you out a bit more than Jose and Brendan have so far but it relies on functions in the more recent builds.

In brief, I made form to see if I could figure a way to count days of the week, like just Fridays, in a date range. I figured a way to do it, the calculation is convoluted and isn’t very easy to read but it seems to work in my testing. I am attaching the form, let me know if you find any bugs and I’ll fix them. It was an interesting challenge to do in TapForms.

The form is best used in the Form Mode but you can use it in the tabular mode if you prefer. It takes a date range defined by start and end date fields you input. It will then tell you the number of Fridays and “Normal” days which are all weekdays less the Fridays. If you give it a pay rate then it will tell you your expected paycheck. I gave it a default pay rate for my testing, just change that in the field parameters. Same with the work hours for normal weekdays and Fridays. I defaulted them as you mentioned in a post 7 and 8 but you can change them or type over them. What-ever, it is flexible. Have fun and let me know if there is a problem.

~Chris

Attachments:

You must be logged in to view attached files.

[Jean-Pierre Hermans]

Hello Chris, thank you for the effort but I don’t see any days between 2 dates. I take as example from December 1 till December 31. But no fridays or other days are show. May a little mistake in the complex calculations?

[Jose Monteiro]

Hi Jean-Pierre,

I was working on a Form for calculating working days between dates to help you, and sent you a primary version which had one bug.
I’ve corrected this first version but did not send the form to you because there was a bug in the DAYS(X;Y) function.
Meanwhile Brendan solved that bug and started developing new date functions which would help solve your problem easily.
So I stopped working on this subject.

I’ve modified my form to adjust it to calculate the number of Fridays between dates.
It just calculates Fridays between dates and is not as complete and elegant as Chris solution.
All those columns from Day1 to Day 6 can be joined (added) in the Fridays Column making the MCLV (Multi-column List View) not so wide, but this would result in a too long formula.
Anyway those columns can be hidden if we want.
While Chris will surely take a look at his solution, if you want, you can try the attached form and see if it works as you need.

Hi Chris,

I love these tricky problems.
I’ve not tested your solution.
On my spare time I’m still trying to understand your complex formula. :)
If I can’t I will ask you to explain me how it works. :)
But after a quick look at it, it seems you are using commas instead of semicolons to separate arguments within IF functions, isn’t that so?

Jose

Attachments:

You must be logged in to view attached files.

[Chris Knight]

Hi Jose,

I will post a little later on the details of the solution I came up with and you can compare it to your thoughts on it.

Yes I am using commas in the IF() statement as it is the default delimiter when you insert it from the function list. That is the only reason I am using them. It seems to work fine with commas. I have not tried semi-colons in the calculation and normally don’t. Is there a reason to?

~Chris

[Chris Knight]

Hi Jean-Pierre,

That is odd. Are you using the Form or multi-column view? Which build of Tap Forms are you using?

Please see the screenshot of the form with the dates you mentioned. This is what it should look like so it will be interesting to understand why it is behaving differently for you. I will be happy to assist to get it working right on your end. Tap Forms should behave exactly the same for everyone so this is curious.

~Chris

Attachments:

You must be logged in to view attached files.

[Jose Monteiro]

Hi Chris,

I just asked because the delimiter I see for all function arguments in my edit formula screen is semi-colons.

Jose

[Chris Knight]

Hi Jose,

That is kind of odd isn’t it? All the functions for me show up with commas as the delimiters.

I thought maybe it might be a localization thing but in my preferences I don’t even have choice to for semi-colon in any of the choices. Maybe it is a region specific localization? What region are you in? I will change my region to match to see if I get semi-colons out of curiosity. My region is the United States and currently all my other localization settings are at the US defaults. I had originally had my week starting on Monday but that caused me some issues in calculations by conflicting with Tap Forms forcing of the week to start on Sunday.

~Chris

[Chris Knight]

Hello Jean-Pierre,

A couple more questions,

1) What OS and device are you using? I built this on an iMac running High Sierra. I don’t have TapForms for iPhone/iPad and don’t even own an iPad so I won’t actually be able to help it is not on a device I own. Sorry about that.
2) I am using a date format code that gets a number for the day of the week, unfortunately this code is only available for localized dates. My computer localization is set to have Sunday the 1st of every week because that is what TapForm functions are expecting. It is possible that an incorrect answer will be obtained if the start of the week is not Sunday. I would not think that it would cause nothing to show up though. What localization are you set on? I will change mine to match yours and see if I can figure out if it has anything to do with it.

~Chris

[Jose Monteiro]

Hi Chris,

I’m in Europe, Portugal, Lisbon.

I remember Brendan discussing this issue some time ago and he concluded that the best would be to have a semi-colon as a delimiter no matter where we are located.
I can’t remember the thread where this took place.
Then, my question about the delimiter.
And probably the problem with Jean-Pierre is the delimiter being a comma.

Jose

[Chris Knight]

Hi Jose,

That is interesting. I will search the forum for the discussion and also change my localization to match yours and see what happens. It will take me a bit to get to that though, I’m in the midst of another post that is kind of wordy. :-)

~Chris

[Chris Knight]

This is a rundown of what the calculations are in the form and the logic behind them.

There are a two date fields. These fields are [Start Date] and [End Date]. They are required fields for the calculated fields [Normal Days Worked] and [Fridays Worked]. Together, they define the range for which to calculate the days worked.

There are three number fields for input to calculate the total pay. [Pay Rate] is formatted for currency and is the wage earned per hour. [Normal Hours] is the number of hours worked per normal weekday, except for Fridays. Normal weekday here is defined as all days of the week except Saturday and Sunday. [Friday Hours] is the number of hours worked on Fridays.

There are three calculated fields.

[Total Pay] is pretty obvious. It totals the hours worked in the input date range and multiplies that by the hourly wage and displays the result as a number formatted for currency. (([Fridays Worked]*[Friday Hours])+([Normal Days Worked]*[Normal Hours]))*[Pay Rate]

[Normal Days Worked] is also clear. It finds the number of weekdays, excluding Saturdays and Sundays, in the defined date range. Then it subtracts the number of Fridays that are in the same date range and displays the result as a number.

[Fridays Worked] is the complicated one. It operates on the idea that Fridays are always seven days apart and that if the given date range is offset to start on a Friday then the duration dived by seven will be the number of Fridays. The formula is actually built so that any day besides Friday could be used but is hard-coded here for Friday. The complete calculation is nesting of several independent calculations needed to answer the question and they were build independently then combined into the calculation on this form.
The steps needed were:
1) Determine what day of the week the start date is.
[Start DOW] = STR2NUM(DATE([Start Date],"e"))
2) Determine when the first DOW in the date range is; this is the day of the week to find within the date range. It is a number form 1-7 and or Friday, it is 6. But in my testing I had this as an numerical input field, and thus variable. This is assumed to be 1=Sunday since that seems to be a TapForms requirement in some functions.
[Weekday] = 6
3) Determine an offset from the start date to the first Friday. Because I wanted my formula to be able to work with any day of the week, not just Fridays, the offset calculation had to account for where the first desired day of week fell in the input date range. The logic has three rules,
A) If the the input start date day of thew week is the same day as the first desired day of week then the offset is 0.
b) If the input start date day of the week comes after the first desired day of the week then an offset defined as [Start DOW]-[Weekday] would be negative so we turn it positive (because we need to next of the desired DOW in the range, not the previous) with ABS([Start DOW]-[Weekday])
c) If the input start date day of the week comes before the first desired day of the week then an offset defined as [Start DOW]-[Weekday] would be greater than the duration to the next desired DOW so with subtract it from the number of days in a week, i.e. seven. 7-([Start DOW]-[Weekday])
Because we have three rules, we need two IF() functions with one nested in the other. The resultant calculation for the offset is:
[Offset to Start] = IF([Start DOW]-[Weekday],IF(([Start DOW]-[Weekday])<0,ABS([Start DOW]-[Weekday]),7-([Start DOW]-[Weekday]) ),0)
4) Create a new start date beginning on the first desired day of the week in the range. This is just adding the offset previously determined to the input start date.
[New Start Date] = DATEADD([Start Date],0,0,0,[Offset to Start],0,0,0)
5) Calculate the number of days from the first Friday to the end of the end date.
6) Divide the number of days in the range by seven, throw away the remainder and this the number of Fridays. I have actually combined steps 6 and 7 in the same calculation in order to get the correct answer. DAYS() is used to find the number of days in the date range now defined as starting at the first occurrence in the input range of the desired day of the week to the end of the input date range. The result is divided by seven to find the number of weeks, thus the number of times the desired day can appear in the input range. Then TRUNC() is being used to eliminate the remainder. Otherwise we have either rounding or a fractional remainder that invalidates the result. We have to add one back in because DAYS() uses subtraction and thus always excludes the first (or last) occurrence of the desired day of the week. TRUNC(DAYS([New Start Date],[End Date]) /7)+1

I am going to have to interrupt this right here and come back to it later. I have the test form I used to build this that might be interesting to look at but I am having major Tap Forms issues right now so cannot access a non-corrupted version of it. I will post it when I get Tap Forms fixed somehow.

~Chris

[Jean-Pierre Hermans]

Hello Chris, I am use tap forms on iPad/iOS with the latest version found in the store and iOS is the latest version. I can not test the mac version because I have it installed anymore and I have to search the proof that i’ve Bought this in 2015. Maybe Brendon can help me with the latest issue! So I have try to test your solution on my iPad and it wasn’t working.
Sorry for the late reply.

[Brendan]

As for the comma vs. semi-colon, for locales where the comma is used as a decimal separator, the default is a semi-colon delimiter. For all others, the delimiter is a comma. That’s just to be compatible with historically using the comma. I should switch it to semi-colon for everyone though, but still have the comma function. So in those regions where a period is used for the decimal point, you can use either a comma or a semi-colon delimiter. But for European countries that use a comma for the decimal point, the semi-colon is required and it will not work with the comma.

I know this is a bit of a mess right now. So it’s easy for someone in Europe to share a template with someone in North America, but the other way around, the Europeans would have to change the commas to be semi-colons in the formulas before they’ll work.

What I’ll do in an update is switch everyone to use the semi-colon by default and the comma will still work for North American customers, but not European customers.

Sorry for the confusion.

So for now if you use semi-colon instead of comma, it will work for everyone.

Boy, imagine a world if everyone agreed upon what delimiters to use for currencies and decimals, for all regions, eh?

[Chris Knight]

Hi Jean-Pierre,

From what Brendan wrote, it sounds like it is likely the commas I used as delimiters. I can change them, and will do so, as soon as I can get my Tap Forms to work again.

I don’t believe it will work though for you without one of the demo versions because it relies on functions that are in the latest builds. I was thinking this Apple TestFlight Brendan mentioned earlier in the thread is the demo version but since I don’t have that I don’t actually know. Also I have no idea how well a form made for the mac will work when run on the iPad so that is a bit of a problem since I can’t test it. If you are willing to test it I will keep making changes until it works :-) I like this kind of stuff.

~Chris

[Brendan]

Hi Chris,

A form built on Mac will work just fine on iOS. There’s no custom layouts, but on iOS you essentially get the Default Layout that you see on Mac.

The TestFlight builds are up-to-date with the Mac betas.

In fact, I’ve published the Mac beta to the free trial version now on my home page.

I’ve been approved to release the Mac version on the Mac App Store.

I’m just waiting for the iOS update to be approved and then I’ll release them.

Thanks,

Brendan

[Chris Knight]

Oh wow. That is good to know. Now all I need to do is figure out how to fix ope the form back up without all the corruption so I can replace the commas with semi-colons. It is too bad there are no custom layouts, but the default layout should work then.

[Chris Knight]

Hello Jean-Pierre,

Thanks to much help from Brendan my Tap Forms is working again. I am attaching an updated version of the from template that has the comas replaced with semi-colons. Please let me know if it works for you now.

To be safe. Delete the old one before importing this one. It turns out that if internal form id numbers are the same it can cause a mess with forms that have the same internal id and I cannot say for sure, though I don’t think it will, if this version will have the same internal id as that last one.

~Chris

Attachments:

You must be logged in to view attached files.

[Brendan]

And also Tap Forms 5.2.4 for Mac and Tap Forms 5.2.3 for iOS are out now, so the new functions I added are available for everyone who gets the update.

[Jean-Pierre Hermans]

Hello Chris, just tested the form but I don’t see the fridays or other days. I see the total days. Tested on iPad.
@Brendan, thanks for the update and adding the function weekdays. Now I have to look how to extract fridays and other workdays separately.

[Chris Knight]

Well that is great news! Now everyone can take advantage of these great new functions.

As I promised in an earlier post in this thread, here is the continuation of that post. I already described how the main function was built from individual functions on a test form. That test form (with commas replaced with semi-colons) is attached here for the curious.

Attachments:

You must be logged in to view attached files.

[Chris Knight]

Hi Jean-Pierre,

So if I understand it right, on the iPad you see only the default layout. I’m kind of confused when you say you see “total days” I didn’t have a field called that. I don’t have an iPad to test this on unfortunately. When you create a new record and have not input any info in it yet, does it look like the screenshot I am attaching? This is what default view looks like on a Mac right after you create a new record.

I am determined to figure out why it works for me and not for you. From what Brendan said, if I replace the commas with semi-colons as I did, it should work anywhere, on any device, using the latest build of Tap Forms.

Attachments:

You must be logged in to view attached files.

[Chris Knight]

I just changed my localization to Belgium and it breaks on my Mac also. This has got to be a localisation problem. I will start working on right now to try and figure out how localisation is breaking it. It might be one of the new functions. I had not tested them except in the US local. I will keep you posted.

~Chris

[Chris Knight]

Hi Jean-Pierre,

Yep it was localisation but not Tap Forms fault. I missed a couple of commas in the function (there were a lot of them) and I had hard-coded 6 for Friday which is for the US. I am using a parameter “e” in the DATE() function that is for the local day in week as a number so there was a conflict. I replaced the hard-coded number with a field for weekday. Just put the day of the week in there for Friday (5 I believe in your local) and all should work now in the version I am attaching now. :-)

~Chris

Attachments:

You must be logged in to view attached files.

[Jean-Pierre Hermans]

Hello Chris, this example works. Now I have the workdays and Fridays and other weekdays are separated.
Thank you very much for your help.

[Chris Knight]

Hello Jean-Pierre,

I am happy to hear that is working for you now. And you are very welcome. Have fun! :-)

~Chris

[Author]

Posts