Net Days between a previous day and today.

JocelynF

I have the following columns

Comments Date of Last Attendance Weeks Not Submitted

Under "Comments", you might see something like "Last Attendance Day: 11/01/2024." I used =RIGHT(Comments@row, 10) to get the Date of Last Attendance. I need to determine how many days it has been since that date using today's date, and divide it by.

Before even trying to divide out the weeks, I am trying to find the formula to get the number of days. I tried =NETDAYS([Last Date of Attendance]@row, TODAY()) and get Invalid Data Type. I tried this was the Date of Last Attendance column as a data column and text column.

What am I missing? Thanks in advance!

Jennifer Kurtz

@JocelynF - Ooooo this is starting to get fun ;)

Will the comments always include the text, "Last Attendance Day:" with the date you're pulling out?

If so, you could alter how you're pulling out the month part of the date - using a more specific "FIND," like this:

=DATE(VALUE(RIGHT(Comments@row, 4)), VALUE(MID(Comments@row, FIND("Day:", Comments@row) + 5, 2)), VALUE(MID(Comments@row, FIND("/", Comments@row) + 1, 2)))

That assumes the text "Day:" will only be included in the comment string once, right before the month. The formula is looking for the month right after the text "Day:" Once it finds that text, you're telling it to start 5 characters later (the first digit of the month) and to pull out two digits for the month.

If your comment string won't always include the "Last Attendance Day" text, you'd probably be better off adding a helper column, with your original formula —

=RIGHT(Comments@row, 10)

and then reference the helper column using the DATE(VALUE pieces to put that in date format.

Hope that helps!

ChristianFinke

Hi @JocelynF

The issue might be that “Last Date of Attendance” is read as text, not a date. Ensure the column is set to “Date” format. If that doesn’t work, try using =NETDAYS(DATEVALUE([Last Date of Attendance]@row), TODAY()) to convert the text to a date format and calculate the days correctly.

JocelynF

Thank you for the response @ChristianFinke . I've tried it with the column set to Date and Text. I also tried your formula and it came back as unparsable.

Jennifer Kurtz

Hi! I don't think there's a DATEVALUE function in Smartsheet (as there is in Excel).

I would suggest changing the formula you're using in the "Last Date of Attendance" column to parse out the date, and change that column to be a Date column.

Try this formula:

=DATE(VALUE(RIGHT(Comments@row, 4)), VALUE(MID(Comments@row, FIND(":", Comments@row) + 2, 2)), VALUE(MID(Comments@row, FIND("/", Comments@row) + 1, 2)))

You'll be converting each part of the date (year, month, day) to a number, and the DATE function will allow Smartsheet to read that as a date. You'll need to use the MID and FIND functions to parse out where the month and day portions of the date are in the text string.

Then, I think your NETDAYS calculation should work fine — and you can parse out the weeks. :)

Does that help?

JocelynF

Hi @jenniferkurtz

Thank you so much! This mostly works. The only issue that sometimes the comments will have an extra string, like "Activities with no Attendance: 1; Last Attendance Day: 10/31/2024". The formula comes up as invalid data. Is there anyway to design the formula so it only picks out the numbers for the date?

Jennifer Kurtz

@JocelynF - Ooooo this is starting to get fun ;)

Will the comments always include the text, "Last Attendance Day:" with the date you're pulling out?

If so, you could alter how you're pulling out the month part of the date - using a more specific "FIND," like this:

=DATE(VALUE(RIGHT(Comments@row, 4)), VALUE(MID(Comments@row, FIND("Day:", Comments@row) + 5, 2)), VALUE(MID(Comments@row, FIND("/", Comments@row) + 1, 2)))

That assumes the text "Day:" will only be included in the comment string once, right before the month. The formula is looking for the month right after the text "Day:" Once it finds that text, you're telling it to start 5 characters later (the first digit of the month) and to pull out two digits for the month.

If your comment string won't always include the "Last Attendance Day" text, you'd probably be better off adding a helper column, with your original formula —

=RIGHT(Comments@row, 10)

and then reference the helper column using the DATE(VALUE pieces to put that in date format.

Hope that helps!

JocelynF

This is amazing! I didn't think about anything beyond the date itself. Thank you so much!!

Jennifer Kurtz

Wonderful! So glad it's working for you!