Measuring inventory levels

[BVCT899111]

Hi good people. I need help calculating the Work In Progress (WIP) inventory for my work.

My Excel spreadsheet has three columns.

• Column A is the item’s unique identification code
• Column B is the time that the item entered the warehouse
• Column C is the time that the item left the warehouse

Is it possible to create an Excel formula in Column D that calculates the inventory level in the warehouse at the time of the item’s entry into the warehouse?

For example, if item 84363884U entered the store at 09:07:23, I would like to know the inventory levels in the warehouse at 09:07:23.

Here is a link to the Excel file for reference. Please and thank you.

 

[FormR]

Hi, welcome to the forum.

Like this maybe - if not, please use a smaller sample of data, fill out your expected results and give us a description of how you arrive at those results.

Calculating WIP.xlsx
	A	B	C	D
1	parcel_id	enter_time	leave_time	WIP inventory at this enter time
2	s11780I658190	08:02:01	08:23:44	1
3	s11780I354406	08:02:02	08:35:05	2
4	s11780I769794	08:02:12	08:30:53	3
5	s11780I298012	08:02:19	08:33:21	4
6	s11780I196167	08:02:37	08:32:23	5
7	s11780I255939	08:02:50	08:35:43	6
8	s11780I789965	08:02:56	08:19:25	7
9	s11780I061877	08:02:57	08:23:31	8
10	s11780I448008	08:03:22	08:31:47	9
11	s11780I691146	08:03:24	08:34:57	10
12	s11780I408432	08:03:26	08:46:39	11
13	s11780I574889	08:03:41	08:33:00	12
14	s11780I166628	08:03:47	08:32:14	13
15	s11780I473770	08:03:57	08:38:10	14
16	s11780I047851	08:04:11	08:28:03	15
17	s11780I581418	08:04:22	08:45:54	16
18	s11780I006130	08:04:24	08:26:58	17
19	s11780I829900	08:04:31	08:29:32	18
20	s11780I662506	08:04:43	08:24:59	19
21	s11780I999833	08:05:15	08:29:00	20
parcelCenter

Cell Formulas
Range	Formula
D2:D21	D2	=COUNTIFS(B:B,"<="&B2,C:C,">="&B2)