Calculate Number of periods between 2 Dates

[Vmanchoppy]

Hi All, Im new and I was wondering if someone might be able to give me some guidance.

If a day is split into 3 time periods starting at 0600, 1400, 2000 and I have 2 dates I am looking for a formula calculate the number periods that fall within the two dates.

An example would be

15/04/2024 14:10 to 17/04/2024 14:13 which would contain 7 periods

15th - 2 periods 1400 and 2000
16th - 3 periods 0600 1400 and 2000
17th - 2 periods 0600 1400

I am looking to try and automate this calculation over a large number of days, and it is specific to those time periods. This might be really simple but is beyond me.

Many thanks

 

[cerberus1845]

you've not said when the finish times are? - or are they?..

Shift one: 0600 - 1400
Shift two: 1400 - 2000
Shift three: 2000 - 0600

also - this link might help in the interim: Calculate hours Night shift - calculate over 24 hours

 

[Vmanchoppy]

you've not said when the finish times are? - or are they?..

Shift one: 0600 - 1400
Shift two: 1400 - 2000
Shift three: 2000 - 0600

also - this link might help in the interim: Calculate hours Night shift - calculate over 24 hours

Sorry, yes as you have stated above for the time periods. It is not shifts but the number of those periods of time a within the two dates to allow a financial calculation for a KPI.

So the 15/04/2024 14:10 to 17/04/2024 14:13 which would contain 7 periods

15th - 2 periods 1400 and 2000
16th - 3 periods 0600 1400 and 2000
17th - 2 periods 0600 1400

or

Another example would be

02/04/2024 15:34 to 17/04/2024 1458 would equal 46 periods
2 periods both on the 2nd (1400 and 2000) and 17th (0600 and 1400) and 14 days of 3 periods in between.

Thanks for the calculation website It is useful. I cannot get my head round the calc.

Cheers again