How to get shift data from timings

[vivekshanghvi]

Hi

I am looking at getting data as to how many shifts did my machine work based on the start and end time? But it cannot be done based on the number of hours...
So basically if a machine was working at a specific time then I need to consider if it is working in that particular shift...

Shift Start Time	Shift End Time
07:30:00​	15:30:00​	1​
15:30:00​	23:30:00​	2​
23:30:00​	07:30:00​	3​

So if the machine is working from say 09:00 hr till 16:30 then I have to consider 2 shifts....

 

[Skybluekid]

Hi

I think I get what you are after. If Machine A has a work time of 09:00 to 16:30, you would like to count how many shifts would be using it. So in the example above there are two shifts that would cover that time period. Is this correct?

 

[vivekshanghvi]

Yes... if you see the image I have manually written the values..

 

[Skybluekid]

Ok, I am not sure where you get the highlighted 2's and 3's from? As if the machine is only working between 09:00 and 16:30, this does not fall into that range.

Book1
	A	B	C	D
6	start time	end time	start shift	end shift
7	09:15:00	21:18:00	1	2
8	14:00:00	00:38:00	1	3
9	21:50:00	06:50:00	2	3
10	08:56:00	12:41:00	1	1
11	12:44:00	23:37:00	1	3
Sheet1

 

[vivekshanghvi]

Hi
It is based on the timings.. as per my first post
So if the machine works after 15:30 hours it is shift 2...
Like if machine is working from
07:30-15:30 it is shift 1
15:30-23:30 it is shift 2
23:30-07:30 it is shift