SOLVED

Getpivotdata with time values working for some not working for others.

%3CLINGO-SUB%20id%3D%22lingo-sub-2904466%22%20slang%3D%22en-US%22%3EGetpivotdata%20with%20time%20values%20working%20for%20some%20not%20working%20for%20others.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2904466%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20having%20trouble%20getting%20GETPIVOTDATA%20to%20work%20with%20Time%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%2C%20pivot%20table%20doesn't%20recognize%20time%20values%20so%20I%20needed%20to%20convert%20them%20to%20general%20format%20in%20my%20data%20source%20and%20base%20my%20pivot%20table%20on%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20got%26nbsp%3B%209%20different%20time%20values%20starting%20from%2008%3A00%20to%2016%3A00%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E08%3A00%20doesnt%20work%20and%2009%3A00%20does%20work.%20I%20don't%20know%20why.%20Can%20you%20help%20me%20out%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20attaching%20a%20dummy%20template.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2904466%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

Hello,

 

I'm having trouble getting GETPIVOTDATA to work with Time values.

 

First, pivot table doesn't recognize time values so I needed to convert them to general format in my data source and base my pivot table on that.

 

I have got  9 different time values starting from 08:00 to 16:00

 

08:00 doesnt work and 09:00 does work. I don't know why. Can you help me out?

I'm attaching a dummy template.

 

Thanks.

 

 

1 Reply
best response confirmed by kheldar (Occasional Contributor)
Solution

@kheldar The data you provided is full with #REF errors, probably referring to real employee names that you deleted (though they still show up when you try to filter the pivot table) and replaced by "Employee 1", "Employee 2" etc. None of these fake employees seem to have time records in the span from 8-16, so I changed some of the #REFs to "Employee 1". And to make the GETPIVOTDATA to work, I changed the timestamps in "hours1" to texts with formula:

=TEXT([@hours],"00")&":00"

Then, I also changed the time labels in A4:A12 in the "Morning" table to plain text. This is the result:

Screenshot 2021-10-31 at 08.29.53.png

The numbers, obviously make no sense because I just picked some time entries for each hour and assigned those to Employee 1. But the formula works.

Having done all that, I just wonder why you don't use one or more pivot tables to do the summarisations for you, without complicated formula. A simple PT (with a filter on the time stamp) could result in this:

Screenshot 2021-10-31 at 08.41.02.png

Same result, just looking a bit different. Up to you.