Calculate start times based on length of shift

[Glasgow Girl]

Hi

I would really appreciate any help with the following.

I need to calculate start times based on various shift lengths.

There are two sessions per day for shifts 09:00 and 14:00. I have used the afternoon shifts in this example

I have created formula for six shifts of different lengths to calculate the start times. However, the spreadsheet I use has 3000-5000 rows with a mixture of shifts in each day. I cannot update each shift manually, based on duration and need one formula to cover six different shifts

The start time is equivalent to 25% of the total length of a shift, which is added to the start time.
So if a shift is:

60 minutes, the start is 15 minutes before 14:00
90 minutes, the start time is 22 minutes before 14:00
120 minutes, the start is 30 minutes before 14:00
150 minutes, the start time is 37 minutes before 14:00
180 minutes, the start time is 45 minutes before 14:00

	C	D	E	F
	START TIME​	END TIME​	LENGTH​	NEW START TIME​
4​	14:00​	14:30​	00:30​	13:53​
5​	14:00​	15:00​	01:00​	13:45​
6​	14:00​	15:30​	01:30​	13:38​
7​	14:00​	16:00​	02:00​	13:30​
8​	14:00​	16:30​	02:30​	13:23​
9​	14:00​	17:00​	03:00​	13:15​

Formula in column F is:
=C4-TIME(0,7,0)-IF(E4<="01:00",TIME(0,ROUNDDOWN(T4*24*7,0),0))
=C5-TIME(0,15,0)-IF(E5="01:00",TIME(0,ROUNDDOWN(T4*24*15,0),0))
=C6-TIME(0,22,0)-IF(E6="01:30",TIME(0,ROUNDDOWN(T5*24*22,0),0))
=C7-TIME(0,30,0)-IF(E7="02:00",TIME(0,ROUNDDOWN(T6*24*30,0),0))
=C8-TIME(0,37,0)-IF(E8="02:30",TIME(0,ROUNDDOWN(T7*24*37,0),0))
=C9-TIME(0,45,0)-IF(E9="02:30",TIME(0,ROUNDDOWN(T8*24*45,0),0))

It would be great if any of you guys could figure this out for me

Thanks

 

[GlennUK]

Try this:

=C4-MROUND(25%*E4,1/(24*60))

 

[Glasgow Girl]

Wow, excellent!
Thank you so much, appreciate you taking the time to help