I need a formula expert to help me write a formula that will calculate how many total WORKDAYS() have passed in the current year. I can’t figure out how to say ‘if year is current year, based on Today’s date, how many workdays have there been since the first of the year’
I want that to return a numeral.
Thanks in advance.
You want the WORKDAY_DIFF() function:
WORKDAY_DIFF(
'1/1/2019',
TODAY(),
[list of holiday dates]
)
To be accurate, your function will need to include the [allegedly] optional list of holiday dates. (That is, the list is optional in that the function can be run without it; it’s not optional if you want it to return a correct value.) To help simplify this process, under the ‘Show and tell’ category I’ve published bases that generate properly formatted ISO date strings for statutory holidays in the US and UK. [The US implementation allows certain unofficial but commonly celebrated holidays — Martin Luther King’s Birthday, the Friday before Labor Day, Columbus Day, Veterans Day, and the Day after Thanksgiving (aka ‘Black Friday’) to be selected; the UK version supports regional variations, with lists appropriate for England, Wales, Scotland, Northern Ireland, and the Republic of Ireland.] Lists spanning multiple years may be generated.
To use, open the base, specify the years and (for the US) any optional holidays or (for the UK) the region desired. The resulting date string returned in the field {Holiday String} can then be copy-and-pasted into your base’s WORKDAY_DIFF() function.
I’ve accomplished this much on my own, I wasn’t clear in that I do not want to have to specify a hard date like ‘1-1-19’ because that limits a dynamic function like ‘how many work days from the beginning of the current month to today’ for example. I want a formula that can specify the beginning of the current year and then I would modify that formula for months and quarters. But I can’t figure out how to make the first fo the year, month,quarter, a function vs a hard coded date that I’d have to change manually.
Thanks for you reply. I enjoy following your work on these forums!
I’ve accomplished this much on my own, I wasn’t clear in that I do not want to have to specify a hard date like ‘1-1-19’ because that limits a dynamic function like ‘how many work days from the beginning of the current month to today’ for example. I want a formula that can specify the beginning of the current year and then I would modify that formula for months and quarters. But I can’t figure out how to make the first fo the year, month,quarter, a function vs a hard coded date that I’d have to change manually.
Thanks for you reply. I enjoy following your work on these forums!
I just responded, based on my reading of your latest reply — only to interpret it entirely differently when I looked back through it to make sure I’d covered everything. So here are both replies, beginning with the one that answers a question I now don’t think you asked. :winking_face:
Now I think I understand what you’re looking for. [Edit: Nope.] To make the previous formula open-ended, all you need to do is replace the hard-coded reference to ‘1-1-2019’ with a reference to a date field named something like {Window Start}. You would set {Window Start} to whatever you wanted to count workdays since. The corresponding formula configuration would be
WORKDAY_DIFF(
{Window Start},
TODAY(),
[list of holiday dates]
)
OK, so I blew that one.
What follows are three formulas to calculate, respectively, the number of workdays so far this year, this quarter, and this month. You could implement them as individual fields and display all three values, or you could control them using a single select and a SWITCH() statement. (I’m providing versions that calculate all components of the start date based on TODAY(), so you won’t need to modify them for 2020 and beyond.)
WORKDAY_DIFF(
DATETIME_PARSE(
'1/1/'&
YEAR(
TODAY()
),
'M/D/YYYY'
),
TODAY(),
[List of holiday dates]
)
Calculating since the beginning of the quarter is a little more complicated, as we have to find a way to translate the quarter number into the corresponding number of its first month. This we do with the following equation:
(((Quarter - 1) * 3) + 1)
For instance, if TODAY() is May 13, then Quarter is 2; 2-1=1; 1*3=3; and 3+1=4, or April, the fourth month, and the first month of Q2.
WORKDAY_DIFF(
DATETIME_PARSE(
(
(
(VALUE(
DATETIME_FORMAT(
TODAY(),
'Q'
)
)-1
)*3
)+1
)&
'/1/'&
YEAR(
TODAY()
),
'M/D/YYYY'
),
TODAY(),
[List of holiday dates]
)
WORKDAY_DIFF(
DATETIME_PARSE(
MONTH(
TODAY()
)&
'/1/'&
YEAR(
TODAY()
),
'M/D/YYYY'
),
TODAY(),
[List of holiday dates]
)
The following assumes a single-select field named {Window} containing options 'Month','Quarter', and 'Year'.
IF(
{Window},
WORKDAY_DIFF(
DATETIME_PARSE(
SWITCH(
{Window},
'Month',
MONTH(
TODAY()
)&
'/1/'&
YEAR(
TODAY()
),
'Quarter',
(
(
(VALUE(
DATETIME_FORMAT(
TODAY(),
'Q'
)
)-1
)*3
)+1
)&
'/1/'&
YEAR(
TODAY()
),
'Year',
'1/1/'&
YEAR(
TODAY()
)
),
'M/D/YYYY'
),
TODAY(),
[List of holiday dates]
)
)
I admit that was a shotgun reply: With such a wide spread, I counted on at least winging your question…
I just responded, based on my reading of your latest reply — only to interpret it entirely differently when I looked back through it to make sure I’d covered everything. So here are both replies, beginning with the one that answers a question I now don’t think you asked. :winking_face:
Now I think I understand what you’re looking for. [Edit: Nope.] To make the previous formula open-ended, all you need to do is replace the hard-coded reference to ‘1-1-2019’ with a reference to a date field named something like {Window Start}. You would set {Window Start} to whatever you wanted to count workdays since. The corresponding formula configuration would be
WORKDAY_DIFF(
{Window Start},
TODAY(),
[list of holiday dates]
)
OK, so I blew that one.
What follows are three formulas to calculate, respectively, the number of workdays so far this year, this quarter, and this month. You could implement them as individual fields and display all three values, or you could control them using a single select and a SWITCH() statement. (I’m providing versions that calculate all components of the start date based on TODAY(), so you won’t need to modify them for 2020 and beyond.)
WORKDAY_DIFF(
DATETIME_PARSE(
'1/1/'&
YEAR(
TODAY()
),
'M/D/YYYY'
),
TODAY(),
[List of holiday dates]
)
Calculating since the beginning of the quarter is a little more complicated, as we have to find a way to translate the quarter number into the corresponding number of its first month. This we do with the following equation:
(((Quarter - 1) * 3) + 1)
For instance, if TODAY() is May 13, then Quarter is 2; 2-1=1; 1*3=3; and 3+1=4, or April, the fourth month, and the first month of Q2.
WORKDAY_DIFF(
DATETIME_PARSE(
(
(
(VALUE(
DATETIME_FORMAT(
TODAY(),
'Q'
)
)-1
)*3
)+1
)&
'/1/'&
YEAR(
TODAY()
),
'M/D/YYYY'
),
TODAY(),
[List of holiday dates]
)
WORKDAY_DIFF(
DATETIME_PARSE(
MONTH(
TODAY()
)&
'/1/'&
YEAR(
TODAY()
),
'M/D/YYYY'
),
TODAY(),
[List of holiday dates]
)
The following assumes a single-select field named {Window} containing options 'Month','Quarter', and 'Year'.
IF(
{Window},
WORKDAY_DIFF(
DATETIME_PARSE(
SWITCH(
{Window},
'Month',
MONTH(
TODAY()
)&
'/1/'&
YEAR(
TODAY()
),
'Quarter',
(
(
(VALUE(
DATETIME_FORMAT(
TODAY(),
'Q'
)
)-1
)*3
)+1
)&
'/1/'&
YEAR(
TODAY()
),
'Year',
'1/1/'&
YEAR(
TODAY()
)
),
'M/D/YYYY'
),
TODAY(),
[List of holiday dates]
)
)
I admit that was a shotgun reply: With such a wide spread, I counted on at least winging your question…
Thank you
:grinning:
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.