Date Formula

CaraBart28 · 01/25/24

Hello,

We are trying to report on the data at the end of each week. Each individual fills in the form each day. What I want to be able to do is total their information every Friday and report the Friday date. So if they enter in today's date, 01/25/2024, I want the helper column to return 01/26/24, however I am receiving "#INVALID COLUMN VALUE." What am I doing wrong?

DKazatsky2 · 01/25/24

@CaraBart28,

Try this.

=[Date of Activities]@row + IF(6 - WEEKDAY([Date of Activities]@row) = 7, (7 - WEEKDAY([Date of Activities]@row)), (6 - WEEKDAY([Date of Activities]@row)))

DKazatsky2 · 01/26/24

@CaraBart28

Check the properties for "Date Helper Column" as well - that needs to be set to "Date".

DKazatsky2 · 01/25/24

Hi @CaraBart28,

This may be failing on the use of "DATEONLY". That function will extract the date portion from a date/time field but you are using it against a field that is already only a date.

Hope this helps,

Dave

CaraBart28 · 01/25/24

@DKazatsky2

Formulas are def not one of my strong points, so I tried removing the date only and I am still not getting a return, what would the formula look like? This is what I have...

DKazatsky2 · 01/25/24

@CaraBart28,

Try this.

=[Date of Activities]@row + IF(6 - WEEKDAY([Date of Activities]@row) = 7, (7 - WEEKDAY([Date of Activities]@row)), (6 - WEEKDAY([Date of Activities]@row)))

CaraBart28 · 01/26/24

@DKazatsky2 It is returning "#Invalid column value"

DKazatsky2 · 01/26/24

@CaraBart28,

2 things to check:

1) Ensure the column with the formula is a DATE type column. This will cause #INVALID COLUMN VALUE

2) Any row with a non-date or blank value in "Date of Activities" column will cause this. This will cause #INVALID DATA TYPE

CaraBart28 · 01/26/24

@DKazatsky2 I confirmed that the properties of the column are set to date and date only. There are no rows within the column that have a value that is not a date.