Hi everybody
I have read many posts on this forum and it has helped me in the past, but this time it seems that I'm really stuck and need to post my actual question (classic story I guess..).
Anyways - I have a dataset that lists shipment orders and various related information. I am trying to create a pivot table to visualize a few KPI's, and my trouble is making calculations based on the count of dates and times. I am using Excel 2010.
Example to explain my problem: I have let's say 100 ETD (estimated time of departure) listings for October 10th. 60 of them also have ATD (actual time of departure) date only, and 40 out of the 100 have ATD date and time. I want the pivot table to calculate the percentages so that it would show that 60% of the ETD's have ATD date and 40% of the ETD's have ATD date and time.
I have no trouble with the dates, I simply add a calculated field that divides the count of ATD dates by the count of ETD dates and format the output as a percentage. Somehow this approach does not work with the times. When adding one more calculated field that divides the count of "ATD date and time" by the count of ETD the result is something like 4,44825E-06. Excel is obviously not calculating what I would like it to calculate and I can't figure out what is really happening, but it looks like Excel does not actually divide the count of cells that state a time of day.
Does anyone in here know what what I am doing wrong? Any help would be much appreciated.
Regards,
BenDixxon
I have read many posts on this forum and it has helped me in the past, but this time it seems that I'm really stuck and need to post my actual question (classic story I guess..).
Anyways - I have a dataset that lists shipment orders and various related information. I am trying to create a pivot table to visualize a few KPI's, and my trouble is making calculations based on the count of dates and times. I am using Excel 2010.
Example to explain my problem: I have let's say 100 ETD (estimated time of departure) listings for October 10th. 60 of them also have ATD (actual time of departure) date only, and 40 out of the 100 have ATD date and time. I want the pivot table to calculate the percentages so that it would show that 60% of the ETD's have ATD date and 40% of the ETD's have ATD date and time.
I have no trouble with the dates, I simply add a calculated field that divides the count of ATD dates by the count of ETD dates and format the output as a percentage. Somehow this approach does not work with the times. When adding one more calculated field that divides the count of "ATD date and time" by the count of ETD the result is something like 4,44825E-06. Excel is obviously not calculating what I would like it to calculate and I can't figure out what is really happening, but it looks like Excel does not actually divide the count of cells that state a time of day.
Does anyone in here know what what I am doing wrong? Any help would be much appreciated.
Regards,
BenDixxon
