Date calculation formulas are not working properly.

Gibrán Alcántara

Hi, community.

I have a weird problem with my metrics sheet and the dashboard linked to it since it is displaying fault information with a specific formula. I have tried a lot of workarounds, including the update of both source and metrics sheet and a lot of different formulas but I can't still fix it.

Formula is as simple as: =COUNTIFS({Dropdown list 1}, "Element 1", {Day}, [Primary Column]@row). Dropdown list 1 is a single selection list with 6 or so elements; Primary Column contains a list of values from 1 to 31 (for each day of the month), and Day is a column containing the following formula: =VALUE(MID(Date@row, 1, 2)); where Date is a column with Date of creation (I have tried both an automatic column and a date column with an automation).

With this, I'm expecting to count the amount of Element 1 rows created per day for the month. Problem is, if I don't come into the source sheet and save it every time a new element is created, the formula gets corrupted and displays weird information. It will display things like counting 25 elements on day 2 when current date is february (2) the 25th (25) and everything else in 0.

To clarify: I am not using Today() formula absolutely anywhere, I'm already using automation to block and unblock new rows to update the source sheet and another automation flux to update the metrics sheet similarly (none of these interfere with the data nor the formulas). Also, I haven't reached the cell nor reference limit in either sheets.

I am hoping to find a real fix for this, since it is not an error with my formula but rather a bug in Smartsheet and manually updating source and metrics sheets is practically impossible in my specific situation because my solution involves around 16 sheets with this behavior and every single one of them is constantly receiving new data along the day (some even have 3 or more inputs in less than an hour) and every time, the metrics sheet gets bugged again.

I'm already sending a ticket to Smartsheet but since they usually take a lot to reply, I brought it here to see if anyone knows how to fix this.

MichaelTCA

Hello @Gibrán Alcántara,

I've had similar issues caused by add-ons. Do you use expressions within data shuttle at all?

Have you tried the COUNTIF function with only 1 criteria? This would be for diagnosing the problem only.

Try using only the Day criteria and see how many results you get that match each day in the list.

If they all have a result, my next question is, does the dropdown column in the source sheet allow for multiple elements? If there are multiple elements you may need to use the CONTAINS() function for "element 1".

I know you said it's a single selection list, but is that marked in the column properties?

Gibrán Alcántara

Hi, Michael!

Thanks for taking the time to read and reply.

I do not use data shuttle nor any other premium app for this solution.

I never use COUNTIF(), but I tried it right now and it didn't work. I also tried using only the "Day criteria" and it didn't work either. The dropdown column type is, indeed, single element and also there are no multiple elements in any row; this value comes from a form that allows a single input only, I also used the formula without the "Day criteria" and it worked as expected.

I noticed that it is possible that the formula is counting the "Element criteria", discarding the "Day criteria" and displaying the total elements only when the month in the dates matches the Primary Column, so it is likely that the bug is related to the position of the date new elements are added to the source sheet.

Finally, I, again, refreshed the source sheet and everything went back to normal this time.

MichaelTCA

@Gibrán Alcántara

I'm going to make a few assumptions so please bear with me.

How is the source sheet set up when referencing the day? You mentioned that the target sheet has the days manually entered, 1-31.

If you are using an actual date, try using a helper to change the value into a text/number in the source sheet.

Then use the same COUNTIFS() function.

The VALUE(MID()) function will work, but only if the starting position and length of characters are all the same.

Usually leading 0s are removed, unless specified as a text with ' , so I can imagine the value being inconsistent down the whole column. You're current function is great for days, greater than 9.

Gibrán Alcántara

Hi, again!

I took a while to make some tests implementing this solution and it seems to be working just fine. So just to clarify in case someone else gets to this point: the issue was practically that the date displayed as XX/XX/XX might be counted as X/XX/XX if the first digit is 0, so, to avoid this issue, simply use DAY and MONTH instead of LEFT, MID or RIGHT.

If I do not reply again here, it will mean the fix worked.

Thanks a lot, Michael, you might just saved my whole solution!