Glasgow Girl
New Member
- Joined
- Mar 9, 2016
- Messages
- 21
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
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
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