countifs date and time

[luvbite38]

Hi guys,


I have a large set of data which contains the logs of every single delivery arrived in a factory. Every delivery has its own record in the register. Against every delivery there is an entry date and time stamped and their exit date/time stamped (See sample below).

Can you please help me calculate the following:

assuming - subject start date 10/02/2013, subject end date 11/02/2013, subject start time 23:45 and subject end time 00:45

- number of deliveries present before subject start date/start time and they remained within start/end dates and times
- number of deliveries present before subject date/start time but they left within the start and end dates and times
- number of deliveries arrived within the start date and time and they remained within start/end dates and times
- number of deliveries arrived within the start date and time but they left within the start and end dates and times

Hope it makes sense???

I am trying to use the countifs formula to calculate these numbers for a range of start/end time and dates.

Please Help

LB

Product	Date Entry	Time Entry	Date Exit	Time Exit
A	11/02/2013	9 AM	12/02/2013	10 AM
B	11/02/2013	9:10 AM	13/02/2013	12:01 PM
C	12/02/2013	12:01 PM	12/02/2013	11:00 AM
D	10/02/2013	11:58 PM	11/02/2013	12:05 PM
E	11/02/2013	12:45 PM	13/02/2013	10:00 pm

<tbody>
</tbody>

 

[luvbite38]

Please some one.....

 

[pdevito3]

So I don't have time to confirm the setup, but something like the below should get the job done. The countifs formula is setup as =countifs(criteria range1,criteria1, criteria range2,criteria2, criteria range N, criteria N)

=COUNTIFS(B8:B13,"< "&DATE(2013,10,2),C8:C13,"< "&TIME(23,45,0),D8:D13,"<" & DATE(2013,2,11),E8:E13,"<" & TIME(0,45,0))

When you use the "Date" and "Time" functions, make sure your months and days, etc. are entered properly. the criteria ranges will be the "date entry" column, "time entry" column, etc.

 

[luvbite38]

Thanks Paul,

Will have a go at it.

Ta

 

[pdevito3]

no problem