**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]

I am pretty sure i have done what you asked in my earlier posts.

can you write an example:
I have a starting date and time of ____________________________.
There is another datetime of ______________________________________.
If the difference of hours between those dates is greater than ______________________________hours,
then add ______________________________________________ hours to this ____________________________________datetime.
If not, add _____________________________________________hours to this ___________________________________datetime.

And if you want to include the time zone difference, please explain how that adjusts the end date/time result? Is only changed at the last formula?

and just curious: Why do you have two different local time differences? Is this for Time Zones?

 

[psc1]

Thank you for your patience and for taking the time to understand

I have a starting date and time of _____01/02/24 @ 0900_____(C2+C3)__________________.
There is another datetime of __________07/02/24 @ 2200_____(C7+C8)______________________.
If the difference of hours between those dates is greater than ______132hrs (C10)________________________hours,
then add ___________26hrs (158-132 or (C7+C8)-(C2+C3)___________________________________ hours to this ______12/02/24 @ 0900 (C12)_________________datetime.
If not, add ___________0hrs_____(just use the current =(C2+C3)+264/24 formula_________________hours to this _________C12__________________________datetime.

 

[psc1]

and just curious: Why do you have two different local time differences? Is this for Time Zones?
This formula is not for time zones, Time zones are calculated only for C6 & C13 cells.
Time zones are important since at the time a task is done on a ship, the time from departure is not the same since the ship is moving through time zones

 

[awoohaw]

add ___________0hrs_____(just use the current =(C2+C3)+264/24 formula_________________hours to this _________C12__________________________datetime

this makes no sense: C2+C3 is a date time value of 45323.375

264/24 has a value of 11​

C12 has a date time value of 45334.375​

So you want a date another 124 years in the future???​

DId you look at the calculations of various dates in Post #10????

Look at what is purple below:

Book1
	A	B	C	D
1
2		Initial Date	2024-02-01
3		Initial Time	09:00:00
4		1st Local Time Difference	-1. Hours
5		Expected Date & Time (TU)	2024-02-06 09:00:00
6		Recommended Date & Time (TU)	2024-02-06 08:00:00
7		Actual Start Date (TU)	2024-02-07
8		Actual Start Time (TU)	22:00:00	2024-02-07 22:00:00
9		Start Original Date & Time (TU)	2024-02-07 23:00:00	^^^^^^ is the same as ^^^^^^^^^
10		Hours Since Initial (120 +/-12hrs)	158:00:00	2024-02-07 22:00:00
11		2nd Local Time Difference	-3. Hours	^^^^^^^^
12		Expected Date & Time (V)	2024-02-12 09:00: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)	2024-02-12 06:00:00
14		Actual Start Date (V)	2024-02-12
15		Actual Start Time (V)	09:00:00
16		Start Time (V)	2024-02-12 12:00:00
17		Total Time from Initial (Min 264hrs)	293:00:00
Sheet2

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

​

 

[psc1]

You are correct, my bad.. C12 currently adds 264hrs or 11 days from C2+C3
So I'm trying to understand.
Can I combine current formula and the one you just made inside C12:
Something like this?
=((C2+C3)+264/24)+(IF((C7+C8)-(C2+C3)>(132/24),(C7+C8),(132/24)))
and this will add the difference in time from (C7+C8)-(C2-C3) only if above 132hrs?

 

[awoohaw]

Look at what I did in purple. And the calculation sheet in POST #10 9.
If it is wrong tell me what is wrong, and what is expected.

I"m really not going to answer any more questions from you until you answer.

After that i'll look at the time difference question.

 

[psc1]

Fair enough and thank you for your time.
I will put my head around those and get back to you.
Thanks again