Formatting a time difference to work in a pivot table

[dan_fash]

OK.... I'm calculating the delay in a patient receiving a certain procedure at my facility. From my spreadsheet I have arrival time, and procedure time, and I've formatted my "delay" box as =(d2-c2)

Easy

Now I need a Pivot table to highlight this data per pt visit by month, so I create a pivot table, but when I try to place my "delay" into the pivot table it inserts a time (12:01:00 AM) instead of a count of minutes ( 0:01). My delay colum is formatted as custom (hh:mm).

Is there a way within the pivot table to change the number formatting, or have I missed something else?

Sorry I can't share my spreadsheet. HIPPA

[Special-K]

Try

=TEXT(D2-C2,"0")

though that will make it text

[oeldere]

you can also format the cells in the pivot table.

[dan_fash]

If I try =TEXT(D2-C2,"0") if fills in the title of my column (arrival time, test performed) instead of the times from the cells

[oeldere]

Post a (small) excel file, without confidential information.

Please also add (manualy) the desired result.

[dan_fash]

pivot table test.xlsx

I think that worked..

[oeldere]

See the attached file.

I was able to change the data in the row, to get the result you asked for.

I made this in excel 2003 (but that should not make any differance).

[dan_fash]

did you make the change to the column in the data table, or within the cells of the pivot table?

[dan_fash]

is there a simple way to convert a time to a specific number, so that in my spread sheet instead of showing 0:03 for a delay, I could just have it say 3 (minutes)?

[dan_fash]

I attempted to do that with vlookup, but it doesnt read the 0:03 as 0:03, it seems to read it as 12:03:00 AM

[oeldere]

did you make the change to the column in the data table, or within the cells of the pivot table?

within the cells of the pivot table.

and to the other question.

if you add the cellformat in the data table on mm:ss (instead of hh:mm) you will get the minutes in the data table.

Of course you can do the same into the pivot table.

[dan_fash]

Cool, thanks

[oeldere]

thanks for the reply.

glad i could help.

if your question is closed will you mark it closed.