counting cells containg date/time

santeria

Well-known Member

Joined

Oct 7, 2003

Messages

1,844

• Dec 31, 2003

• #2

So, are you summarising the Hours for each date/day...etc ?

Sort of a tabulation for each day?

 

Upvote 0

P

pacoonejr

Board Regular

Joined

Apr 19, 2003

Messages

138

• Dec 31, 2003

• #3

actually what it is is a count of each row for each shift

every row for each 8 hour time period

if there where 30 rows less than 14:00 then i would need 30

etc etc

 

Upvote 0

P

pacoonejr

Board Regular

Joined

Apr 19, 2003

Messages

138

• Dec 31, 2003

• #4

I am so confused

 

Upvote 0

H

herilane

New Member

Joined

Dec 30, 2003

Messages

21

• Dec 31, 2003

• #5

I would add an extra column to the sheet, where you calculate which shift the row belongs to. Then use a pivot table to summarise the data.

 

Upvote 0

santeria

Well-known Member

Joined

Oct 7, 2003

Messages

1,844

• Dec 31, 2003

• #6

Are you intending to add the total occurences of each time event, as in 06:00 happened 18 times, or to summarise the total number of hours?

I think maybe a little more detail is required... I suspect.

pacoonejr said:

I am so confused

Click to expand...

 

Upvote 0

I

IML

MrExcel MVP

Joined

Feb 15, 2002

Messages

1,743

• Dec 31, 2003

• #7

For a formula solution, you could use
=SUMPRODUCT((HOUR($A$1:$A$10)>=6)*(HOUR($A$1:$A$10)<14))
and a similar formula for 14-22 shift. With passing midnight on your other shift, try
=SUMPRODUCT(((HOUR($A$1:$A$10)>=22)+(HOUR($A$1:$A$10)<6))*($A$1:$A$10<>0))

where the last argument will not falsely count blank cells.

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Dec 31, 2003

• #8

pacoonejr said:

column E is full of dates and times

12/26 06:00
through
12/27 06:00

i need to count everything from 06:00 TO 13:59
from 14:00 to 21:59 AND from 22:00 TO 06:00

the issue is this report is pulled daily
each day the date changes.

so here i am at the house full of smart people begging
thanks in advance......

Click to expand...

Hi pac:

If I have understood you correctly, try this ...

Book2
	D	E	F	G	H	I
1		DateAndTime	ifhourincolumnGissmaller
2		12/26/20035:00	itisconsideredtobeininthefollowingday
3		12/26/20035:55
4		12/26/20036:50	6:00	13:59	8
5		12/26/20037:45	14:00	21:59	6
6		12/26/20038:40	22:00	6:00	2
7		12/26/20039:35
8		12/26/200310:30
9		12/26/200311:25
10		12/26/200312:20
11		12/26/200313:15
12		12/26/200314:10
13		12/26/200315:05
14		12/26/200316:00
15		12/26/200316:55
16		12/26/200317:50
17		12/26/200318:45
18		12/26/200322:30
19		12/27/20032:15
20		12/27/20036:00
Sheet2

The formula in cell H4 is ...

Code:

=COUNTIF($E$2:$E$26,">="&INT($E2)+F4)-COUNTIF($E$2:$E$26,">="&INT($E2)+G4+(G4<F4))

and this is then copied over to cells H5 and H6.

 

Upvote 0