Retaining Date column type for output of Pivot App when pivoting by a Date-type field

Emma Stanford

I have a Pivot sheet that looks like this:

It pulls values from a large report containing rows pulled from ~10 different sheets. With help from a few helper columns, the output looks like this:

I use the "Combined" and "NumberImages" values to populate dashboard charts of monthly progress (with reports as an intermediary to clean things up a bit). That all works fine.

However, I also want to be able to generate yearly reports for each financial year (September-August), meaning I need to be able to filter the pivot sheet by a date range, from September 2021 to August 2022, for instance. I can't figure out how to do this, because the "Rows" column type is Text/Number, not Date. If I try to extract a date value in a helper column using YEAR() or MONTH() or DATE(), I get an invalid data type error.

I thought the problem might be the complexity of the nested years and months, so I tried a new pivot using "None" as the row grouping, meaning there's just one line for each date. I ran into the same problem with an invalid data type error when I tried to extract date information into a date-type column:

I added a new Text/Number column, copied the Rows value into it, and then switched the column type to Date. This got the date values in Rows to render as dates:

However, this only worked for the rows that already existed before I created the Date column and switched the column type. If I added a new row, that row value did not appear as a date (see 2022-01-30 below). So I would need to repeat the step of adding the Date column each time I ran the pivot.

I also tried slicing up the Rows value into YYYY, MM and DD so that I could concatenate them in a DATE() function, but got the same invalid data type error:

One weird thing is that in a way, Smartsheet doesn't seem to realize that 2022-01-30 isn't a date. If I change the second column's type to Date and say "Restrict to dates only", I get a message saying 2 cells aren't dates, when in fact there are 3 that are functionally not recognized as dates, meaning I can't perform any date functions or filtering on them: "<BLANK>", "2022-01-30", and "Grand (Total)".

To get around this problem, the only thing I can think of to do is to create a sheet with an INDEX(COLLECT) formula that collects all the NumberImages values from the rows in my pivot sheet where the Combined value is equal to any of the strings "September 2021", "October 2021", etc. up through "August 2022". But that's quite fiddly. It seems like there ought to be a way for Pivot to group things by date without removing the semantic value of the dates it's grouping by. Is there something I'm missing about how DATE() and other date-related functions work?

Sorry for the long post, thank you in advance for any suggestions!

Genevieve P.

Hi @Emma Stanford

Thank you for all of this detail!

The solution you have where you parse out the Year, Month, and Day into columns then bring them into a DATE function should work, except I see that your formula is outputting the values on the left of the cell. This indicates that it's being read as Text, not Numerical to put into a DATE function.

Try wrapping VALUE() around each of your referenced cells, like so:

=DATE(VALUE(Year@row), VALUE(Month@row), VALUE(Day@row))

You'll still get an error on rows that contain text, so you can wrap this whole formula in an IFERROR:

=IFERROR(DATE(VALUE(Year@row), VALUE(Month@row), VALUE(Day@row)), "")

Let me know if this worked for you!

Cheers,

Genevieve

Genevieve P.

Hi @Emma Stanford

Thank you for all of this detail!

The solution you have where you parse out the Year, Month, and Day into columns then bring them into a DATE function should work, except I see that your formula is outputting the values on the left of the cell. This indicates that it's being read as Text, not Numerical to put into a DATE function.

Try wrapping VALUE() around each of your referenced cells, like so:

=DATE(VALUE(Year@row), VALUE(Month@row), VALUE(Day@row))

You'll still get an error on rows that contain text, so you can wrap this whole formula in an IFERROR:

=IFERROR(DATE(VALUE(Year@row), VALUE(Month@row), VALUE(Day@row)), "")

Let me know if this worked for you!

Cheers,

Genevieve

Emma Stanford

Thanks for your help, Genevieve! Using VALUE does seem to work for non-grouped date pivots. I'm able to parse out month and year using the VALUE, LEFT and RIGHT functions, and to create a whole date based on the pivot date output with this formula:

=DATE(VALUE(LEFT(Rows@row, 4)), VALUE(LEFT(RIGHT(Rows@row, 5), 2)), VALUE(RIGHT(Rows@row, 2)))

I was also able to use VALUE to extract the year from a grouped date pivot, but not the month:

Do you know of any way to parse the month value out of a grouped date pivot (with January nested inside 2022, etc.)?

Emma Stanford

Hi! Update: I was able to get a YYYY/MM/DD column based on my grouped date pivot by using a helper "Month #" column that's basically just a matrix of month names and numbers:

=IF(Rows@row = "January", 1, IF(Rows@row = "February", 2, IF(Rows@row = "March", 3, IF(Rows@row = "April", 4, IF(Rows@row = "May", 5, IF(Rows@row = "June", 6, IF(Rows@row = "July", 7, IF(Rows@row = "August", 8, IF(Rows@row = "September", 9, IF(Rows@row = "October", 10, IF(Rows@row = "November", 11, IF(Rows@row = "December", 12))))))))))))

Not the most elegant solution, but it saves me having to create another pivot.