PivotTable Calculated Item error

[michaelacumming]

I'm new to working with calculated items in PivotTables, and I've run
into a bit of a snag.

My PivotTable is built off of a data set with a start and stop time (in
military time) for a number of workers. Included in the data set as
well is a worktype. I have a calculated field in my Pivot which returns
the # of hours worked by way of the following formula:

(EndTime-StartTime)*24

I have this data displayed by work type and date on the top, and by 1/2
hour interval of the start time on the left (the data set as well is
restricted to half hour intervals). This part works great!

For certain reasons, I need to subtract the hours worked within one
work type from the hours worked within another work type. This is where
I'm running into problems with a calculated item. For certain half hour
intervals, this works. However, for others, I'm getting wrong #'s, and
I can't even determine how Excel is calculating this. I think that it
has something to do with my formula above, as I am able to introduce
another column in my data set with a list of integers, and the
calculated item works great.

Any ideas? I'd be happy to share my file (or a screenshot), if that
would be helpful.

Thanks!

Michael Cumming
(e-mail address removed) , (e-mail address removed)

 

[Roger Govier]

Hi Michael

If you are getting lots of "####'s" it sounds as though the time calculation
result is negative. Excel doesn't like negative time, unless you are using
the 1904 date system (which can bring with it other complications).

Maybe the calculation where you are subtracting one time from another needs
modifying. Try instead
=MOD((A1-B1),1)
where A1 and B1 hold your times, and see if that resolves the problem.

Regards

Roger Govier