**Add existing date & time to another cell with condition**

[psc1]

Hello
Would one of you genius out there please help me out with a formula?
There is one cell that needs to change its Date & Time based on the value of a previous one.
But it only needs to add the date & time difference between those 2 cells IF result is above 132hrs (only add whatever is above 132hrs)
Hopefully someone can work it out.
Cheers

NEW - D&T Formula Update.xlsx
	A	B	C	D	E	F	G	H
1
2		Initial Date	1/09/2023
3		Initial Time	9:00
4		1st Local Time Difference	0
5		Expected Date & Time (TU)	6/09/2023 9:00
6		Recommended Date & Time (TU)	6/09/2023 9:00
7		Actual Start Date (TU)	6/09/2023
8		Actual Start Time (TU)	21:08
9		Start Original Date & Time (TU)	6/09/2023 21:08
10		Hours Since Initial (120 +/-12hrs)	132:15
11		2nd Local Time Difference	0
12		Expected Date & Time (V)	12/09/2023 9:00	<<<- How to add date & time difference to this cell only if C7+C8>132hrs (only for the time above 132hrs)
13		Recommended Start Date & Time (V)	12/09/2023 9:00		/ would something like this work? =(C2+C3)+264/24+IF(C7+C8)>=132/24
14		Actual Start Date (V)	12/09/2023
15		Actual Start Time (V)	9:00
16		Start Time (V)	12/09/2023 9:00
17		Total Time from Initial (Min 264hrs)	264:15
18
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	=MROUND(C9-(C2+C3),15/(60*24))
C12	C12	=(C2+C3)+264/24
C17	C17	=MROUND(C16-(C2+C3)+IF(C10>=(132/24),C10-132/24,0),15/(60*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]

what cell is what?
You have two "time difference" cells and zero date difference cells.

 

[psc1]

hello awoohaw
I cannot add new cells to this for simplicity of use.
Cell to be modified is C12 and must take into account C7+C8 only if C7+C8 is above 132 (then add whatever extra value to C12 On top of what C12 is calculating by itself)
Hope it makes sense..

 

[awoohaw]

no, how is the "extra value" calculated?
And what do you want if it doesn't exceed 132?

=IF(C7+C8>132,C12+WHAT,WHAT)?

 

[psc1]

ok got you..
If it doesn't exceed 132 then C12 only does this: =(C2+C3)+264/24
If C7+C8 exceed 132 then the added result gets added to C12 (only what is above 132)
i.e if C7+C8 = 133hrs then 1hr is added to C12 (on top of C12 current formula)
Tricky part is to add this extra time and make it look like a Date & Time format.. if 25hrs are above 132 (=157hrs) then C12 should read 1 Day (calendar/date format) & 1 hour (time format) on top of what C12 is currently calculating.
Let me know if I make sense

 

[awoohaw]

Are you just asking how to make the one calculation that adds extra time (days and hours) to the original date and time?
I sense that you are troubled with date/time calculations. They confuse me sometimes, too. But, I think you are over thinking it.
It seems that you have a starting date/time. Why break it up into two separate components of your formulas.
The date/time format of yyyy-mm-dd hh:mm:ss displays the time.
But in all of your discussions all I interpret is:

What is the new recommended date/time: If the elapsed time from Initial Date to Expected Date is less than 11 days (132 hours) add 11 days to the Initial Date, otherwise use the expected Date/time.

(you have various other dates, times, and adjustments in the worksheet, but they aren't used in the question above.

Book1
	B	C
19	Initial Date Time	2023-09-01 09:00:00
20	Start Date/Time	2023-09-06 21:08:00
21	Elapsed Time	132:08:00
22	Expected Date/Time	2023-09-06 21:08:00
23	Expected Date/Time Rounded to nearest 15 min	2023-09-06 21:15:00
Sheet1

Cell Formulas
Range	Formula
C21	C21	=C20-C19
C22	C22	=C19+IF(C21<(132/24),132/24,C21)
C23	C23	=MROUND(C19+IF(C21<(132/24),132/24,C21),15/(24*60))

I'm not really sure if this answers your question. If it doesn't, just tell what is your data is, and what do you want out of it. Add to that any conditions (like time zone adjustment) and how that affects your desired result.

 

[psc1]

Since you did last fix for me I have not been able to finish this one..
I really appreciate your help with this and will try to explain what I would like to achieve.

In a practical situation, C2 & C3 are never changed.
C5 & C6 will not change either as they are given as a guide line taken from C2 & C3.

But C7 & C8 will be filled in by the user and sometimes can go over the recommended or expected time (C5 & C6).

In this situation, if C10 equals more than 132 then whatever the difference is from 132 to this result must be added to C17.
Which you did help me with last time.

All I need to do now is to adjust C12 Time Date format to take into account that time if any is added from 132

 

[awoohaw]

I thought this thread is familiar, why have you created a different profile?
And your statments are spaghetti to me. I cannot follow.

I am pretty sure the formula I give in C23 is what you want.

C2 + C3 = this sum is not in a cell but combined is is a Date/Time, lets called it DateTime0
C5 and C6 I have no idea what role they play in this calculation.
C7 + C8 = this sum is not in a cell but combined is a Date/Time lets called it DateTime1
C9 Looks like you're adding a time adjustment to Time1, lets call this DateTime2
C10 You're rounding Time2 to the nearest 15 minutes, call this DateTime3
C12 You want to to add the Maximum of :

1. DateTime2 minus DateTime0​

2. 132/24 (11 Days)​

to DateTime0​

If that is right, I do that in the above, in C22 and C23, only the references are different..

I just don't know where in the formulas you want any rounding, as it is in some place and not in others

 

[awoohaw]

take a loook at this, and tell me if the calculations here are what you want.
NOTE: I am not doing anything with the time zone adjustment or what ever you called it You have zeros in it in your work, so i'm ignoring it.

Book1
	A	B	C	D	E	F	G
1
2
3	DateTime0	DateTime1	time adj	Minimum Expected Time	expected datetime adjustment	expected datetime	expected datetime rounded
4	2023/09/01 09:00:00	2023/09/03 06:59:00		132:00:00	5.5	2023/09/06 21:00:00	2023/09/06 21:00:00
5	2023/09/01 09:00:00	2023/09/03 12:41:00		132:00:00	5.5	2023/09/06 21:00:00	2023/09/06 21:00:00
6	2023/09/01 09:00:00	2023/09/04 13:57:00		132:00:00	5.5	2023/09/06 21:00:00	2023/09/06 21:00:00
7	2023/09/01 09:00:00	2023/09/05 17:02:00		132:00:00	5.5	2023/09/06 21:00:00	2023/09/06 21:00:00
8	2023/09/01 09:00:00	2023/09/06 22:51:00		132:00:00	5.577083333	2023/09/06 22:51:00	2023/09/06 22:45:00
9	2023/09/01 09:00:00	2023/09/07 22:58:00		132:00:00	6.581944444	2023/09/07 22:58:00	2023/09/07 23:00:00
10	2023/09/01 09:00:00	2023/09/08 19:17:00		132:00:00	7.428472222	2023/09/08 19:17:00	2023/09/08 19:15:00
11	2023/09/01 09:00:00	2023/09/09 15:03:00		132:00:00	8.252083333	2023/09/09 15:03:00	2023/09/09 15:00:00
12	2023/09/01 09:00:00	2023/09/10 19:10:00		132:00:00	9.423611111	2023/09/10 19:10:00	2023/09/10 19:15:00
13	2023/09/01 09:00:00	2023/09/11 16:13:00		132:00:00	10.30069444	2023/09/11 16:13:00	2023/09/11 16:15:00
14	2023/09/01 09:00:00	2023/09/12 15:15:00		132:00:00	11.26041667	2023/09/12 15:15:00	2023/09/12 15:15:00
15	2023/09/01 09:00:00	2023/09/13 12:29:00		132:00:00	12.14513889	2023/09/13 12:29:00	2023/09/13 12:30:00
16	2023/09/01 09:00:00	2023/09/14 09:43:00		132:00:00	13.02986111	2023/09/14 09:43:00	2023/09/14 09:45:00
17	2023/09/01 09:00:00	2023/09/15 21:55:00		132:00:00	14.53819444	2023/09/15 21:55:00	2023/09/15 22:00:00
18	2023/09/01 09:00:00	2023/09/16 12:32:00		132:00:00	15.14722222	2023/09/16 12:32:00	2023/09/16 12:30:00
19	2023/09/01 09:00:00	2023/09/17 16:36:00		132:00:00	16.31666667	2023/09/17 16:36:00	2023/09/17 16:30:00
Sheet1

Cell Formulas
Range	Formula
D4:D19	D4	=132/24
E4:E19	E4	=MAX(B4-A4,D4)
F4:F19	F4	=A4+E4
G4:G19	G4	=MROUND(A4+E4,15/(24*60))

 

[psc1]

Thanks for your help and apology for confusing description, I am struggling with that date-time thing..
I have used the same profile as last thread you helped me with.
I only need 1 cell formula to be adapted in C12, working in conjunction with current formula in same cell.
To make it simple,
What formula can be used to only add hours difference that exceed 132hrs from (C7+C8)-(C2+C3)
Cheers

NEW - D&T Formula Update.xlsx
	A	B	C	D
1
2		Initial Date	1/02/2024
3		Initial Time	9:00
4		1st Local Time Difference	-1
5		Expected Date & Time (TU)	6/02/2024 9:00
6		Recommended Date & Time (TU)	6/02/2024 8:00
7		Actual Start Date (TU)	7/02/2024
8		Actual Start Time (TU)	22:00
9		Start Original Date & Time (TU)	7/02/2024 23:00
10		Hours Since Initial (120 +/-12hrs)	158:00
11		2nd Local Time Difference	-3
12		Expected Date & Time (V)	12/02/2024 9:00	<<<- How to add hours difference (in date & time format) between (C7+C8)-(C2+C3)>132hrs (but only if this number is greater than 132)
13		Recommended Start Date & Time (V)	12/02/2024 6:00
14		Actual Start Date (V)	12/02/2024
15		Actual Start Time (V)	9:00
16		Start Time (V)	12/02/2024 12:00
17		Total Time from Initial (Min 264hrs)	293:00
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	=MROUND(C9-(C2+C3),15/(60*24))
C12	C12	=(C2+C3)+264/24
C17	C17	=MROUND(C16-(C2+C3)+IF(C10>=(132/24),C10-132/24,0),15/(60*24))

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