Time conversion to existing formula

[psc1]

Hi All
I got myself a headache trying to work this one out..
I am sure one of you experts out there will get me going
2 cells to modify, I already have the hours but I need to show the minutes in actual time and not decimals but rounded to the nearest 15min.
Also, I need to add to a cell time from another cell but only if it goes beyond a value (132hrs) Then add the difference (whatever is above 132).
Please see attached document.
Many thanks

D&T Test.xlsx
	A	B	C	D	E	F
1
2		Initial Date	1/09/2023
3		Initial Time	7:00
4		1st Local Time Difference	0
5		Expected Date & Time (TU)	6/09/2023 7:00
6		Recommended Date & Time (TU)	6/09/2023 7:00
7		Actual Start Date (TU)	6/09/2023
8		Actual Start Time (TU)	7:22
9		Start Original Date & Time (TU)	6/09/2023 7:22
10		Hours Since Initial (120 +/-12hrs)	120.3666667	How to read decimal in minutes? Can it be rounded to the nearest 15 minutes?
11		2nd Local Time Difference	0
12		Expected Date & Time (V)	12/09/2023 7:00
13		Recommended Start Date & Time (V)	12/09/2023 7:00
14		Actual Start Date (V)	12/09/2023
15		Actual Start Time (V)	7:12
16		Start Time (V)	12/09/2023 7:12
17		Total Time from Initial (Min 264hrs)	264.2	How to read decimal in minutes? Can it be rounded to the nearest 15 minutes?
18				Also, C17 needs to take into account additional hours IF C10 goes above 132hrs
19				C17 must add hours from C10 ONLY if they are above 132
20
Sheet1

Cell Formulas
Range	Formula
C5	C5	=(C2+C3)+120/24
C6,C13	C6	=C5+(C4/24)
C9,C16	C9	=(C7+C8)-(C4/24)
C10	C10	=(C9-(C2+C3))*24
C12	C12	=(C2+C3)+264/24
C17	C17	=(C16-(C2+C3))*24

Cells with Data Validation
Cell	Allow	Criteria
F3	List	=Sheet2!$B$4:$B$12
F6	List	=Sheet2!$B$4:$B$12
C4	List	=Sheet2!$B$2:$B$14
C11	List	=Sheet2!$B$2:$B$14

 

[psc1]

This might need to be asked on a separate thread
Thank you so much for you help with this.. Much appreciated.

 

[awoohaw]

is such a formula exist?

I just googled and found MROUND. No new thread needed.

Book1
	A	B	C	D	E
1
2		Initial Date	2023-09-01
3		Initial Time	07:00:00
4		1st Local Time Difference	0
5		Expected Date & Time (TU)	2023-09-06 07:00:00
6		Recommended Date & Time (TU)	2023-09-06 07:00:00
7		Actual Start Date (TU)	2023-09-06
8		Actual Start Time (TU)	07:17:00
9		Start Original Date & Time (TU)	2023-09-06 07:17:00
10		Hours Since Initial (120 +/-12hrs)	120.2833333	120hr30min	How to read decimal in minutes? Can it be rounded to the nearest 15 minutes?
11		2nd Local Time Difference	0	120hr17min	custom format: [hh]"hr"mm[min]
12		Expected Date & Time (V)	2023-09-12 07:00:00	120hr15min
13		Recommended Start Date & Time (V)	2023-09-12 07:00:00
14		Actual Start Date (V)	2023-09-12
15		Actual Start Time (V)	07:12:00
16		Start Time (V)	2023-09-12 07:12:00
17		Total Time from Initial (Min 264hrs)	264.2	264hr15min	How to read decimal in minutes? Can it be rounded to the nearest 15 minutes?
18				264hr12min	Also, C17 needs to take into account additional hours IF C10 goes above 132hrs
19				264hr15min	C17 must add hours from C10 ONLY if they are above 132
Sheet3

Cell Formulas
Range	Formula
C5	C5	=(C2+C3)+120/24
C6,C13	C6	=C5+(C4/24)
C9,C16	C9	=(C7+C8)-(C4/24)
C10	C10	=(C9-(C2+C3))*24
D10	D10	=CEILING(C9-(C2+C3),15/(60*24))
D11	D11	=(C9-(C2+C3))
D12	D12	=MROUND(C9-(C2+C3),15/(24*60))
C12	C12	=(C2+C3)+264/24
C17	C17	=(C16-(C2+C3))*24
D17	D17	=CEILING(C16-(C2+C3)+IF(D10>=(132/24),D10-132/24,0),15/(60*24))
D18	D18	=C16-(C2+C3)+IF(D10>=(132/24),D10-132/24,0)
D19	D19	=MROUND(C16-(C2+C3)+IF(D10>=(132/24),D10-132/24,0),15/(60*24))

 

[psc1]

Brilliant.. Thanks much

 

[awoohaw]

Brilliant.. Thanks much

My pleasure, best wishes.