Formula

Can any expert let me know if smartsheet is smart enough to provide number of days from a date column and Status column which is a text column showing several text options. When someone change the status from the drop down menu for example forwarded, cancelled and completed etc, is there any formula that can work to give result in number of days as soon as status changes say today. Again one is DATE column and other is TEXT/NUMBER column.

Thanks

Find more posts tagged with

Comments

JamesR

Abdul,

You need two date columns to be able to calculate date difference and there are a couple of functions that can do it, see Help:

WORKDAY(): Returns a date that is the specified number of working days before or after a date.

Example: =WORKDAY([Due Date]5, 6)
Result: 3/10/14 (US date format)

You can designate other dates as non-working to exclude them when calculating the new date. To do this, enter each holiday/non-working day into a cell and then reference the range of cells in your WORKDAY formula.

Syntax: WORKDAY(start, num_of_days, holiday:holiday)
Example: =WORKDAY([Due Date]1, 365, [Due Date]2:[Due Date]3)
Result: 3/30/15 (US date format)

NETDAYS(): Returns the number of days between two dates.

Example: =NETDAYS([Due Date]4, [Due Date]5)
Result: 29

NOTE: This formula doesn't take into account holidays or non-working days.

NETWORKDAYS(): Returns the number of working days between a start date and end date.

Example: =NETWORKDAYS([Due Date]4, [Due Date]5)
Result: 20

You can optionally exclude holidays or other dates from the count. To do this, enter each holiday into a cell and then reference the range of cells in your NETWORKDAYS formula.

Syntax: NETWORKDAYS(start, end, holiday:holiday)
Example: =NETWORKDAYS([Due Date]1, [Due Date]5, [Due Date]2:[Due Date]3)
Result: 85

NETWORKDAY(): Same, but adds 1 to the result if the first argument is a non-working day.

Example: =NETWORKDAY([Due Date]4, [Due Date]5)
Result: 21

You can designate additional dates as non-working to exclude them when calculating the number of working days. To do this, enter each holiday/non-working day into a cell and then reference the range of cells in your NETWORKDAY formula.

Syntax: NETWORKDAY(start, end, holiday:holiday)
Example: =NETWORKDAY([Due Date]1, [Due Date]5, [Due Date]2:[Due Date]3)
Result: 85

It the status column has dates as text then that also can be addressed but slightly more complicated.

Travis

Are you looking for the number of days between a date and when a cell was updated? If the status is the last item to be updated in a row, you can use the Modified (Date) System column (timestamp of when the row was last modified) and the date to calculate the number of days between the two.

Sreevidhya Sankaran

Very useful. Where do I find these formulas that are available.

Thanks

sree

Kennedy Stomps

Hi Sree-- All of the operators currently available in Smartsheet are listed here with examples of how they work. You can also check out our Formula Examples template to see all of the operators live in a sheet. Hope this helps!

Sreevidhya Sankaran

Thank you Kennedy. That was very helpful. :-)

Maggie F.

Hello, How do I combine two formulas into one cell? Here's what I'm trying to do:

Each of these formulas works, but I'd like to combine them so that if a task is not yet completed it shows up as "open". If it is completed, it shows how many days it took to complete.

=NETWORKDAYS([Date completed]1, [Date submitted]1)

=IF(ISBLANK([Date completed]1), "Open")

I appreciate any advice!

Manini

I am trying to calculate the number of days between Today - Issue Open Date, below formula gives me #INVALID DATA TYPE

=NETDAYS(TODAY(), [Issue Open Date]1)

Like Today - 5/19/2019 = 1

JamesR

Is [Issue Open Date] a date column?

If its not you will need to use the Date()

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