Calculating Days

Jacob Judd

Hello

How do i get this formula to Calculating the numbers of Days when i select the date columns.

e.g I want to select the dates and then it calculates the number of days from the two date ranges selected.

Mike Wilday

Do you want workdays or calendar days? 

Work Days =networkdays([Date Column 1]24, [Date Column 2]24)

Calendar Days =netdays([Date Column 1]24, [Date Column 2]24)

replace the Date Column 1 and Date Column 2 with the actual column name. 24 assumes this is row 24. 

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

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

Enjoy.

JLK

I'm trying to do this and I keep getting the result of #INVALID DATA TYPE. I've checked my helper column properties, and both are "date". They have been defaulted to MM/DD/YY by smartsheet. Do I need a different date layout for the formula to work?

On a side note, when I originally import the data, my dates include timestamps, but because I know smartsheet doesn't play well with time, I've taken to adding helper columns that change it to just the date format... could the helper columns be the issue here?

Paul Newcome

@JLK It depends on what formula(s) you have in your helper columns. What formula(s) are you using exactly?

JLK

Hi Paul,

Thanks for helping me figure this out. My formula for the helper column is:

=[name of column]@row

which should return exactly whats in my original date + timestamp column (formatted as: YYYY-MM-DD 00:00:00), but then I apply the column property of date and it returns just the MM/DD/YY

If there is a better way to do this I'm all ears.

Paul Newcome

https://community.smartsheet.com/discussion/comment/395225#Comment_395225

Are you able to provide some screenshots for context?

JLK

Here is the column as imported:

And here is the helper column formula: =[date applied (UCT)]@row

And here is what the helper returns:

It used to not return the time, but when I logged back in to get you these screenshots, the time appears to still be there, so probably the first step is getting rid of the times?

Paul Newcome

So even though you are using a date column, you are still only populating a text string. To get just the date pulled out of the string and stored as a date value, you would use something like this:

=DATE(VALUE(LEFT([date applied (UTC)]@row, 4)), VALUE(MID([date applied (UTC)]@row, 6, 2)), VALUE(MID([date applied (UTC)]@row, 8, 2)))

JLK

Thanks Paul,

What I'm getting when I use this formula is #Invalid Value... thoughts?

Paul Newcome

Sorry about that. I miscounted the number of characters. Try this instead:

=DATE(VALUE(LEFT([date applied (UTC)]@row, 4)), VALUE(MID([date applied (UTC)]@row, 6, 2)), VALUE(MID([date applied (UTC)]@row, 9, 2)))