I'd like to create a column that generates a number of working days between the finish date in a row

Chris McLaughlin · 09/15/20

I'd like to create a column that generates a number of working days between the finish date in a row and today.

So, if today is Sept 15, and a task is supposed to finish on September 18, the cell in the column would display "3". What formula should I use?

Similarly, is there a variable in SmartSheet formulas to represent today's date? (like $today, etc.)

Stefan · 09/16/20

Hi @Chris McLaughlin,

actually you can write nearly the same formula from your example:

=Finish@row - TODAY()

delivers the number of days (including weekend etc.).

Does this help, or did I still misunderstand your question?

Greetings

Stefan

Stefan · 09/16/20

Hi @Chris McLaughlin,

glad I could help.

Regarding holidays, there is no magic needed. Just go to your project settings and edit the working days. There is a field where you can enter public holidays. These will be used if you also activate dependencies.

Hope this helps too

Stefan

Stefan · 09/16/20

Hi @Chris McLaughlin,

you should use

https://help.smartsheet.com/function/networkdays

And yes, TODAY is also available:

https://help.smartsheet.com/function/today

Hope this helps

Stefan

Chris McLaughlin · 09/16/20

Thanks for weighing in, but it doesn't quite get to what I'm asking.

I am aware of those two functions. What I'm looking for is a variable, so I could write a formula like =Finish-$TODAY and the answer would be "3". I'm hoping I don't need a column in my spreadsheet that contains today's date!

Thanks,

Chris

Stefan · 09/16/20

Hi @Chris McLaughlin,

actually you can write nearly the same formula from your example:

=Finish@row - TODAY()

delivers the number of days (including weekend etc.).

Does this help, or did I still misunderstand your question?

Greetings

Stefan

Chris McLaughlin · 09/16/20

Cool! I ended up using "=NETWORKDAYS(TODAY(), Finish1)" so it would only tell me working days.

It would be even better if it automatically (automagically?) did not include holidays on the sheet, but I'll take what I can get! :)

Thank you,

Chris

Stefan · 09/16/20

Hi @Chris McLaughlin,

glad I could help.

Regarding holidays, there is no magic needed. Just go to your project settings and edit the working days. There is a field where you can enter public holidays. These will be used if you also activate dependencies.

Hope this helps too

Stefan

I'd like to create a column that generates a number of working days between the finish date in a row

Best Answers

Answers

Help Article Resources

Categories