This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Other questions
- Re: Formula to count workdays between now and a du...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

2
1613
0

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 28, 2017 09:58 PM

Hi, all!

I’m looking to modify a formula I pulled from the “Freelancers Dashboard” template in Universe. The end goal is to calculate the number of working days between now and a due date in another field. The use case is calculating how many workdays I have left before I need to invoice a client.

The template’s (super complicated) formula works, but you have to manually enter how many weekends remain, and it only counts down how many days are left in the month, not down to a date field. As a use case, this isn’t helpful for clients billed on a (bi)weekly cadence.

This is how the formula is now.

I know there’s a DATETIME_DIFF and WORKDAY function, but I can’t figure out how to piece them together and include the “Next Invoice” field. Any help is appreciated!

6 Replies 6

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 29, 2017 06:01 AM

I suspect you could start with the formula I posted in this reply, which calculates…

No; wait.

I think you could use that formula, unchanged, to get what you need. (It calculates the number of workdays between two dates; however, it doesn’t take into account holidays.) I can’t think of any reason for it not to work if you replace `{Date from}`

with `TODAY()`

.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 29, 2017 07:30 PM

Thanks for your reply!

The formula works counting days left in the current month, but you have to manually enter how many weekend days and holidays remain at the end where the two 0’s are.

Regardless, I don’t want to count down days left in the month, I want to count down workdays to the date listed in the “Next invoice” field in each record.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 30, 2017 02:40 AM

I figured I must have misread your original post, so I copied the freelancer dashboard template, modified it, and posted my modification — and then re-read your most recent reply and realized I *hadn’t* misunderstood you. Here’s the solution you want; I’ll post the other approach (that is, the one that automates the days-left-in-month calculation) after.

This is the formula from my reply to an earlier message on the forum.

`DATETIME_DIFF({Date to},{Date from},'days')-`

`(INT(DATETIME_DIFF({Date to},{Date from},'days')/7)+`

`IF(DATETIME_FORMAT({Date from},'E')>DATETIME_FORMAT({Date to},'E'),1,0))*2`

To count the number of work days between today and the next invoice date, replace `{Date to}`

with `{Next invoice}`

and `{Date from}`

with `TODAY()`

, as so:

`DATETIME_DIFF({Next invoice},TODAY(),'days')-`

`(INT(DATETIME_DIFF({Next invoice},TODAY(),'days')/7)+`

`IF(DATETIME_FORMAT(TODAY(),'E')>DATETIME_FORMAT({Next invoice},'E'),1,0))*2`

This doesn’t support the ‘anticipated days off’ functionality of the original formula, but it should give you the number of workdays until the next invoice date.

**NOTE:** My original formula assumed both beginning and ending dates always fell on a workday; I’ve not tested to see what happens when `TODAY()`

is on a weekend.

What follows is the first version of my reply, with the days-left-in-month calculation.

. ____________________________________________________________

I tracked down the template you used, and I *think* this does what you want. I’m not entirely sure, though, as 15 minutes ago this became the worst day of the month to test this algorithm.

I’ve also not attempted to plug this into the existing dashboard. Also, the original formula includes a cheat to allow for planned days off to be subtracted from the ‘days left’ calculation; if you need that functionality, you’ll have to add it to the formula.

I’ve created a demo ‘base’ that contains only the three fields (two new, one modified) you will need to add this modification to your existing dashboard. (To keep things cleaner and more easily maintained, I perform the `{Days Left}`

calculation in three separate pieces, a field for each. You can combine them into a single formula, if you wish.)

Briefly, I’ve created two new fields and replaced the formula for the existing `{Days Left}`

field.

`{Month}`

formula field

Formula:

`MONTH(TODAY())`

`{EndOfMonth}`

formula field

Formula:

`DATETIME_PARSE(DATETIME_FORMAT(TODAY(),'MM')&'/'&`

`IF(Month=1,'31',IF(Month=2,'28',IF(Month=3,'31',IF(Month=4,'30',`

`IF(Month=5,'31',IF(Month=6,'30',IF(Month=7,'31',IF(Month=8,'31',`

`IF(Month=9,'30',IF(Month=10,'31',IF(Month=11,'30',IF(Month=12,'31')`

`)))))))))))&'/'&DATETIME_FORMAT(TODAY(),'YYYY'),'MM/DD/YYYY')`

`{Days Left}`

formula field

Replace formula with:

`DATETIME_DIFF(EndOfMonth,TODAY(),'days')-`

`(INT(DATETIME_DIFF(EndOfMonth,TODAY(),'days')/7)+`

`IF(DATETIME_FORMAT(TODAY(),'E')>DATETIME_FORMAT(EndOfMonth,'E'),1,0))*2`

Again, you’ll probably want to see how this code works tomorrow. :winking_face:

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 30, 2017 10:24 AM

Yes! Thanks so much for the help.

I was my misunderstanding; I didn’t realize in your first reply you were referring to the formula you linked and not the one I already had, where there was no {Date from}. That’s why I was confused!

I’m okay with it not counting holidays. I tested it adding more records with fake dates and it works like a charm. You’re the best!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 30, 2017 10:31 AM

No problem – after I posted my last reply I realized how overly ambiguous my first response had been. I’ve got to start hitting the support forum before midnight. :winking_face:

Glad it helped!

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 02, 2018 03:19 PM

In case you were interested in moving off of the workaround… we’ve just added a WORKDAY_DIFF formula! Documentation here.

It takes in a start date, end date, and optional list of holidays, like so: `WORKDAY_DIFF({Assignment date}, {Due Date}, '2017-09-04, 2017-10-09, 2017-11-10')`

, and returns the number of working days between the start date and end date, inclusive.

For those of you familiar with Excel or Sheets, it’s pretty much isomorphic to the `NETWORKDAYS`

formula in those products.

Reply