Can someone please help me with a time formula. I have a start date and time in columns A and B and then blocks of 4 hour chunks in column C onwards. I would a formula that will tell me how many hours (up to a max of 4) goes in each column chunk. I am just getting myself tied up in knots so help would be much appreciated.
Book2.xlsx | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
2 | 23:00 to 03:00 (4 hours) | 03:00 to 07:00 (4 Hours) | 07:00 to 11:00 | 11:00 to 15:00 | 15:00 to 19:00 | 19:00 to 23:00 | 23:00 to 03:00 next day | ||||
3 | Start Date and Time | End Date and Time | 01/11/2020 23:00 | 02/11/2020 03:00 | 02/11/2020 07:00 | 02/11/2020 11:00 | 02/11/2020 15:00 | 02/11/2020 19:00 | 02/11/2020 23:00 | ||
4 | 01/11/2020 23:30 | 02/11/2020 02:45 | 3 hours 15 min | zero mins fall in this block | zero mins fall in this block | zero mins fall in this block | zero mins fall in this block | zero mins fall in this block | zero mins fall in this block | ||
5 | 01/11/2020 00:25 | 02/11/2020 20:45 | 2 hours 35 min | 4 hours | 4 hours | 4 hours | 4 hours | 1 hour 45 min | zero mins fall in this block | ||
6 | 01/11/2020 03:45 | 02/11/2020 04:45 | zero mins fall in this block | 1 hour | zero mins fall in this block | zero mins fall in this block | zero mins fall in this block | zero mins fall in this block | zero mins fall in this block | ||
7 | 02/11/2020 06:55 | 02/11/2020 16:07 | zero mins fall in this block | 5 mins | 4 hours | 4 hours | 1 hour 7 mins | zero mins fall in this block | zero mins fall in this block | ||
Sheet5 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D3:I3 | D3 | =C3+(4/24) |