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

 

[awoohaw]

You can use the FLOOR function to truncate to 15 minute intervals. But, I'm not sure what you want regarding your adding C10 if over 132. Are you adding only the difference above 132 or all of it? You can also use custom formatting to display your hours and minutes.
Take a look:

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:22:00
9		Start Original Date & Time (TU)	2023-09-06 07:22:00
10		Hours Since Initial (120 +/-12hrs)	120.3666667	120hr15min	How to read decimal in minutes? Can it be rounded to the nearest 15 minutes?
11		2nd Local Time Difference	0		custom format: [hh]"hr"mm[min]
12		Expected Date & Time (V)	2023-09-12 07:00:00
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	264hr00min	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
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	=FLOOR(C9-(C2+C3),15/(60*24))
C12	C12	=(C2+C3)+264/24
C17	C17	=(C16-(C2+C3))*24
D17	D17	=FLOOR(C16-(C2+C3),15/(60*24))+IF(D10>=(132/24),132/2,0)

 

[psc1]

That is clever thanks for your help
Regarding C17.. Yes anything above 132 from C10 is added to C17 (not all only >132)
I have no clue how to do this.. Is your current modification doing this?

 

[awoohaw]

That is clever thanks for your help
Regarding C17.. Yes anything above 132 from C10 is added to C17 (not all only >132)
I have no clue how to do this.. Is your current modification doing this?

It is not, change the formula to subtract the d10 value in the last part of the formula.
Something like this:

=FLOOR(C16-(C2+C3)+IF(D10>=(132/24),D10-132/24,0),15/(60*24))

(this is updated to apply the floor to the entire value).

Note I made my formula changes in column D, not C. You'll need to adjust accordingly.

Also, if you want to have the 15 minutes rounded up, use CEILING function instead of FLOOR.

 

[psc1]

Great solution.
What sort of cell formatting you used to display result in Hours & Minutes "h:mm"?
I cannot get same results as you..

 

[awoohaw]

I made some updates to my post in the last 4 minutes. You may need to reveiw them again.
The custom format is:
[hh]"hr"mm"min".

 

[psc1]

Legend
Thanks for this I got it now..
Quick question please:
Currently it rounds up to the lowest 15 min (i.e 28min on clock shows as15min)
Is there a different formula that combines Ceiling or Floor that could round to the nearest 15min.. Up or Down? (i.e 29min =30 & 12min = 15 or 6min =0)
Cheers

 

[awoohaw]

Yes, just replace FLOOR with CEILING.
for cell D17:

=CEILING(C16-(C2+C3)+IF(D10>=(132/24),D10-132/24,0),15/(60*24))

As far as I know you can only choose one. You can't Floor at 22 minutes and Ceiling at 22 min 1 sec. Although there are probably some formula tricks that can coerce to the 15 minute interval you want.

 

[psc1]

Just did but now it is rounding up
I meant if time is 07:06am it should show 07:00 but if Time is 07:09, it should read 07:15 (nearest 15 minutes either up or down based on exact clock)
Cheers

 

[psc1]

is such a formula exist?