Date and Time display in Pivot Table

On my data sheet I have the following format:

Company Name Scheduled Time Actual Time Number variance
Joe Doe 06/01/10 08:15 AM 06/01/10 08:15 AM 0.010416667
John Brown 06/03/10 05:12 AM 06/03/10 05:12 AM -0.033333333
Joe Doe 06/08/10 12:55 PM 06/08/10 12:55 PM 1.288194444
... ... ...

In my pivot table, I want to display the Avg number variance for each company in the following time format:

0 hour(s) 15 min(s) Late/Early/Ontime

Which is this formula:
=IF('Num Var' =0,"On Time",TEXT(ABS('Num Var' ),"[h]"" hour(s) ""m"" min(s) """)&IF('Num Var' <0," Early","Late"))

but when I enter that into calculate field formula area, it gives me an error #VALUE

I think a calculated field needs to return a number not a text string (it needs to be something that can be subtotalled....

Maybe perform the formula outside the pivot table?

I am not the greatest with custom formatting , but I don't think you can format a cell (adding text strings) based on the result of another.... You can use conditional formatting which will change the colour or font style.. you can then perhaps use and colour coding index which tells user what the colour means?

If you don't include the actual time in the format... because negative time will result in ###############... then use custom format:

"Late";"Early";"On-Time"

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

As mentioned, I don't think you can do as you requested in a Pivot table.. you would need to apply the formulas outside the Pivot table.

Why not the same formula you used in the main sheet, just referencing the pivot table column instead?

I am not sure what you mean... the <0 condition takes care of negative values.. no?

Note that switching to 1904 systems keeps like that until you switch it back.. so other workbooks you open/use will be adapted to that system.

Post the new (unrelated question) in a new thread.