SUMPRODUCT and Time problem

[Jack]

Hi everyone, I have a problem with using SUMPRODUCT and a range of time data (hours) from a report dumped out of our system as CSV, copied into my workbook.


My formula is:

=SUMPRODUCT((ApptAnalysis!A$13:A$1000>=B5)*(ApptAnalysis!A$13:A$1000<B6)*(ApptAnalysis!B$13:B$1000=KPIs!B$3)*(ApptAnalysis!I$13:I$1000))


B5 is the 1st of a month

B6 is the 1st of the next month

B3 is a dentist name code

I13:I1000 is hours for each dentist in a given month.


A cell in this I13:I1000 range looks like 93:00:00 but in the edit line it looks like this 3/01/1900 9:00:00 PM. If you remove the formatting it is a value of 3.88 which is days. 3.88 x 24 = 93.


My formula is:

=SUMPRODUCT((ApptAnalysis!A$13:A$1000>=B5)*(ApptAnalysis!A$13:A$1000<B6)*(ApptAnalysis!B$13:B$1000=KPIs!B$3)*(ApptAnalysis!I$13:I$1000))


This formula works with other ranges in this data like dollars or numbers of patients but won't work on this time range I13:I1000.


I have tried removing the formatting, still doesn't work. At first I thought the data might be text but I have tested the number with a VBA UDF and it confirms the cells are values. I can add to them for instance so I think they are definitely values. I have googled a lot on this and not finding a problem SUMPRODUCT has with time as I guess it's just a number right?


Any ideas?


Thanks everyone


John

 

[keymaster]

Hi John...


As you may know, time is nothing but a value between 0 to 1. Each day in Excel is 1 number, so each hour is 1/24. This 6:00 hrs would be 0.25. So if a dentist logged 93:00 hrs, it would be 93/24 = 3.88. Since 3.88 can be represented as a date, Excel correctly shows it as 3/01/1900 9:00 PM (this is because, in Excel world 0 represents 1st of Jan 1900, mid night).


As far as I know, your sumproduct formula should correctly sum up the hours. Once you have the result, just format it to show only in hours. To do this, select the cell with result, go to format cells (CTRL+1) and then use the format code [h]:mm


PS: to understand how date & time work, visit http://chandoo.org/wp/2008/08/26/date-time-tips-ms-excel/

 

[Jack]

Hi and many thanks Chandoo, I thought I was going nuts. The formula should work but it doesn't for some reason. I am not getting the days returned I am getting another number that makes no sense. After many frustrating attempts I have given up and gone for the following workaround index/match array formula - the 9th column being the hours I wanted.


=INDEX(ApptAnalysis!A$13:Z$1000,MATCH(1,(ApptAnalysis!A$13:A$1000>=B5)*(ApptAnalysis!A$13:A$1000<B6)*(ApptAnalysis!B$13:B$1000=KPIs!B$3),0),9)*24 {array entered}


Maybe I will look at it afresh one day and try again with SUMPRODUCT.


Cheers

John