PivotApp Output: Dates will not show as values

Hello all!

I am trying to setup a pivot app that looks similar to the screen snip below. This is how it was setup in Excel before we started moving to Smartsheets.

The sheet I am using to create the pivot from looks like this:

My pivot settings are:

I would like my Payers as my columns and my Provider Name then Line of Business as the rows. I would like the PAR Effective Date to be the value that returns. However, I am getting all blank cells or 0 on my destination sheet. I read somewhere that you have to break apart your dates, so I tried that also:

The formulas I used to do this are:

PAR Month: =LEFT([PAR Effective Date]@row, 2)

PAR Day: =MID([PAR Effective Date]@row, 4, 2)

PAR Year: =RIGHT([PAR Effective Date]@row, 2)

Combined PAR Date: =[PAR Month]@row + "/" + [PAR Day]@row + "/" + [PAR Year]@row

Basically, my ideal output would look something like this:

Any tips or recommendations on what I am doing wrong? I've never had such a hard time with a pivot table and am starting to regret making this $12k investment in transitioning to Smartsheets when extremely simple things like pivot tables are so complicated.

Find more posts tagged with

Accepted answers

All comments

LGarcia2022

Hey Mason -

So the screenshot of your ideal output reminds me of a report. I would try taking a stab at that instead. I've come across instances where I can't get the pivot to be exactly what I'm looking for so I'll take that pivot and turn it into a report or sometimes I just go straight to a report. There's probably an easier solution sometimes, but I'm learning as I go. :)

I'd suggest creating a report using your smartsheet file. You can determine what columns, filters, groups, and summaries you want. The report feature is fairly user friendly.

Hope that helps!

Kate Desrochers

Just following up here-- has there been any luck with this? I can't get a report to work, because I'm looking to transfer what was a row (ie an item in a schedule) into a column (using that column to report when the milestone was achieved, for multiple projects which are each in their own row).

I am demo'ing Pivot, and I'm concerned that this will not fix our issue and we'll likely need to transition away from smartsheet if we can't figure this out.

Thanks,

Kate

Paul Newcome

You shouldn't have to break apart your dates, but if you are going that route, then you need to make sure you are outputting numerical and date values (respectively).

LEFT, MID, and RIGHT functions all output text strings, and to get a formula to output a date value, you need to use the DATE function.

Try these instead:

PAR Month:

=VALUE(LEFT([PAR Effective Date]@row, 2))

PAR Day:

=VALUE(MID([PAR Effective Date]@row, 4, 2))

PAR Year:

=VALUE("20" + RIGHT([PAR Effective Date]@row, 2))

Combined PAR Date:

=DATE([PAR Year]@row, [PAR Month]@row, [PAR Day]@row)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.