Counting date cells

[paddysheeran]

Hi All,

i've recently been given a task to count the number of incidents which have been logged between 10am and 4pm monday to friday. I need a forumla which will query coulmn D of my spreadsheet (which holds all the incident logged dates - formatted as dd/mm/yyyy hh:mm)

I tried to start with the following to pick up the incidents which fell between 10 am and 4 pm:

=SUMPRODUCT(--(Incidents_Logged!$D$2:$D$65000>=TIME(6,0,0)),--(Incidents_Logged!$D$2:$D$65000<TIME(16,0,0)))

this just returns a result of 0

I've searched around but cant seen to find any examples of what I require. Any help would be greatly appreciated.

thanks,

paddy.

[mbarron]

Your problem lies with the fact that you are comparing a date to a time. You need to parse the date/time field to it's time component:

=SUMPRODUCT((TIME(HOUR(C2:C22),MINUTE(C2:C22),SECOND(C2:C22))>=TIME(10,0,0))*(TIME(HOUR(C2:C22),MINUTE(C2:C22),SECOND(C2:C22))<=TIME(16,0,0))*1)

qoute tags used to avoid wrapping of text

[Bob Phillips]

If Mr Barron is correct about the test data then you can do it far more simply with

=SUMPRODUCT(--(MOD(Incidents_Logged!$D$2:$D$65,1)>=--"06:00"),
--(MOD(Incidents_Logged!$D$2:$D$65,1)<--"16:00"))

[paddysheeran]

thanks for that. does this formula only count monday to friday dates?

could you be so kind as to explain each part of the forumal so i can gain an understanding for future reference.

many thanks.

[mbarron]

Since your demo formula only looked at the time function, I assumed that the dates were already Monday through Friday.

=SUMPRODUCT(--(MOD(C2:C22,1)>=--"10:00")*--(MOD(C2:C22,1)<=--"16:00")*(WEEKDAY(C2:C22)>1)*(WEEKDAY(C2:C22)<7))

Excel stores date and times as numbers. The integer part of the number is the number of days since 0 day or Dec 31, 1900. The decimal part of the number is part of a day past midnight. For example Jan 15 2010 at 6am is 40193.25

Mod(Date,1) will return the decimal part of a date/time. The -- before the text of "10:00" forces excel to interpret the text as a time. A better explanation is here (http://mcgimpsey.com/excel/formulae/doubleneg.html)).

[Bob Phillips]

Since your demo formula only looked at the time function, I assumed that the dates were already Monday through Friday.

=SUMPRODUCT(--(MOD(C2:C22,1)>=--"10:00")*--(MOD(C2:C22,1)<=--"16:00")*(WEEKDAY(C2:C22)>1)*(WEEKDAY(C2:C22)<7))

Again, this can be simplified

=SUMPRODUCT(--(MOD(C2:C22,1)>=--"10:00")*--(MOD(C2:C22,1)<=--"16:00"),--(WEEKDAY(C2:C22,2)<=5))

There is no point in mixing double unary with a * operator


A better explanation is here (http://mcgimpsey.com/excel/formulae/doubleneg.html)).

An even better explanation is here (http://xldynamic.com/source/xld.SUMPRODUCT.html)

[paddysheeran]

Hi all,

thanks for you help the following appears to be working now after doing a manual count to confirm:

=SUMPRODUCT(--(MOD(Incidents_Logged!$D$2:$D$65000,1)>=--"06:00"),--(MOD(Incidents_Logged!$D$2:$D$65000,1)<--"16:00"),--(WEEKDAY(D2:D65000,2)<=5))

I need to add an extra condition to this formula to discount any date which falls on a national holiday. I have a list of these dates in a seperate sheet called "Holidays". The dates are listed in column A. How would I incorporate this into the calculation?

again I appreciate all your help with this.

thanks,

Paddy.

[paddysheeran]

Also as an afterthought can you exaplin what the "--" signify in the forumla. thanks.

[Bob Phillips]

Hi all,

thanks for you help the following appears to be working now after doing a manual count to confirm:

=SUMPRODUCT(--(MOD(Incidents_Logged!$D$2:$D$65000,1)>=--"06:00"),--(MOD(Incidents_Logged!$D$2:$D$65000,1)<--"16:00"),--(WEEKDAY(D2:D65000,2)<=5))

I need to add an extra condition to this formula to discount any date which falls on a national holiday. I have a list of these dates in a seperate sheet called "Holidays". The dates are listed in column A. How would I incorporate this into the calculation?

I don't really understand. If you are counting incidents between certain times, how do holidays affect it? Either the incident happened on a holiday between those times, or it doesn't get looged on a holiday.

[Bob Phillips]

Also as an afterthought can you exaplin what the "--" signify in the forumla. thanks.

See http://xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation

[paddysheeran]

Hi there , the formula is going to be used to capture incidents that have been logged "in hours" which covers all incidents logged Monday - Friday 06:00 am - 04:00 PM. If an incident was logged on a bank holiday this is classed as out of hours. As most bank holidays fall on a monday I need the forumla to check whether the incident logged date matches with any dates listed in the Holidays sheet. This could also cover christmas day and the easter period.

[paddysheeran]

Hi All,

I need one final condition to the formula

=SUMPRODUCT(--(MOD(Incidents_Logged!$D$2:$D$65000,1)>=--"06:00"),--(MOD(Incidents_Logged!$D$2:$D$65000,1)<--"16:00"),--(WEEKDAY(Incidents_Logged!$D$2:$D$65000,2)<=5))


Column P of the incidents logged spreadsheet is populated with country names (e.g brazil, Uk etc)

if i wanted to count the number of incidents for Brazil which were logged between 06:00 and 16:00 monday to friday how would I amend the forumal above to capture this?

many thanks,

Paddy.

[Bob Phillips]

TRy

=SUMPRODUCT(
--(Incidents_Logged!$P$2:$P$65000="Brazil")
--(MOD(Incidents_Logged!$D$2:$D$65000,1)>=--"06:00"),
--(MOD(Incidents_Logged!$D$2:$D$65000,1)<--"16:00"),
--(WEEKDAY(Incidents_Logged!$D$2:$D$65000,2)<=5))

[blaqk]

Im confuzzld :S

[Bob Phillips]

Im confuzzld :S

By what?

[paddysheeran]

yes:

=SUMPRODUCT(
--(Incidents_Logged!$P$2:$P$65000="Brazil"),
--(MOD(Incidents_Logged!$D$2:$D$65000,1)>=--"06:00"),
--(MOD(Incidents_Logged!$D$2:$D$65000,1)<--"16:00"),
--(WEEKDAY(Incidents_Logged!$D$2:$D$65000,2)<=5))

extra comma has been added after "Brazil") which makes it work. Do you have any idea how to discount dates that are listed in column A of the holidays sheet?

[Bob Phillips]

As I said before, and I didn't get your response, I don't see why you would, and if you do, how you discount it.