Help with Date Auto-Populate, Countifs & Reporting

virtualfreelance

Hi Team,

I hope everyone is well! My name is Patrick and I'm a new SS user and was wondering if I can ask for help with the following:

1 Auto-populate the date if columns Agent and Ticket# are filled in.

2 Countifs formula is not capturing 07/11

3 Chart Reporting date to auto-change weekly based on next week's dates

Please check the video below:

https://www.loom.com/share/3fabe838e05a4f1f88e2a251a475ba03

Best regards,

Patrick

Genevieve P.

@virtualfreelance

Hey Patrick! Glad you could join us 🙂

1) For your first question, it looks like you're using a System Created date that will add a Date/Time every time a new row is created (or one cell is filled in on that row, regardless of the column). What I would recommend is setting up an Automation on your sheet to Record a Date when both of your cells are filled in!

Keep in mind that the Date column here would need to be a regular type of Date column. You'll also want to make sure that Condition Block is checking to see that both cells are not blank so that it only runs once both cells have been filled in.

Here's the documentation on this type of workflow: Set the current date with the Record a Date action

2) For your second issue with the Dates, the incorrect number may actually be due to Timezones. System Date Columns display dates based on your timezone, but the underlying data is recorded in UTC, and this is what the formula is looking at.

Changing your date column to be a Regular Date column and using the workflow above should resolve this for you!

3) For the chart, what you can do here is create a Report from that sheet which uses a Filter for the Dates, then use the Report as the source for your chart.

The Report will automatically update each week to only bring in the relevant rows, which will then update your chart automatically. See: Build a row report and Create Filter Criteria to Control Data in Report Builder

Let me know if I can help clarify anything further!

Cheers,

Genevieve