Travel Duration allowing for more than one day and different time zones

[ozbeachbum]

=IF(Y19="DST",(W19-U19)-TIMEVALUE("01:00"),IF(Y19="EST",(W19-U19)+TIMEVALUE("01:00"),
IF(AND(F19>D19,Y19="DST",(F19-D19)-DAY(1)+W19+DAY(1)-U19)-TIMEVALUE("01:00"),
IF(AND(F19>D19,Y19="EST",(F19-D19)-DAY(1)+W19+DAY(1)-U19)+TIMEVALUE("01:00"),
IF(F19>D19,(F19-D19)-DAY(1)+W19+DAY(1)-U19,W19-U19)))))
The formulae in BOLD work, I am having trouble getting multiple days and different time zones to work.
Any assistance greatly appreciated.

GG 24-25 C&S financials.xlsx
	D	E	F	G														U	V	W	X	Y	Z	AA	AB	AC	AD
13		Date
14	Start		End															Depart		Arrive		Time Zone		[hh]:mm Duration
15
16
17
18
19	Thu 21-Nov-24		Thu 21-Nov-24															07:20		10:40		EST		04:20
20	Thu 21-Nov-24		Fri 22-Nov-24															08:00		10:00				26:00		CORRECT	26:00
21	Fri 22-Nov-24		Sat 23-Nov-24															08:00		07:00				23:00		CORRECT	23:00
22	Sun 24-Nov-24		Tue 26-Nov-24															20:00		07:00				35:00		CORRECT	35:00
23	Wed 27-Nov-24		Fri 29-Nov-24															20:00		07:00		DST		##########		Would need to be	34:00
Blank (5)

Cell Formulas
Range	Formula
AC20:AC23	AC20	=IF(AA20=AD20, "CORRECT","Would need to be")
AD20:AD21	AD20	=W20-U20+DAY(1)
AD22	AD22	=W22-U22+DAY(2)
AD23	AD23	=(W23-U23-TIMEVALUE("01:00"))+DAY(2)
AA19:AA23	AA19	=IF(Y19="DST",(W19-U19)-TIMEVALUE("01:00"),IF(Y19="EST",(W19-U19)+TIMEVALUE("01:00"), IF(AND(F19>D19,Y19="DST",(F19-D19)-DAY(1)+W19+DAY(1)-U19)-TIMEVALUE("01:00"), IF(AND(F19>D19,Y19="EST",(F19-D19)-DAY(1)+W19+DAY(1)-U19)+TIMEVALUE("01:00"), IF(F19>D19,(F19-D19)-DAY(1)+W19+DAY(1)-U19,W19-U19)))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AA19:AB28	Cell Value	=0	text	YES

 

[myall_blues]

What about this Dave?

=IF(Y20="EST",((F20+W20)-(D20+U20)+TIMEVALUE("01:00"))*24,IF(Y20="DST",((F20+W20)-(D20+U20)-TIMEVALUE("01:00"))*24,((F20+W20)-(D20+U20))*24))

 

[bebo021999]

Book1
	D	E	F	G														U	V	W	X	Y	Z	AA
13		Date
14	Start		End															Depart		Arrive		Time Zone		[hh]:mm Duration
15
16
17
18
19	21-11-24		21-11-24															7:20		10:40		EST		04:20
20	21-11-24		22-11-24															8:00		10:00				26:00
21	22-11-24		23-11-24															8:00		7:00				23:00
22	24-11-24		26-11-24															20:00		7:00				35:00
23	27-11-24		29-11-24															20:00		7:00		DST		34:00
Sheet1

Cell Formulas
Range	Formula
AA19:AA23	AA19	=F19+W19-D19-U19+IF(Y19="EST",1,IF(Y19="DST",-1,0))*1/24

 

[ozbeachbum]

What about this Dave?

=IF(Y20="EST",((F20+W20)-(D20+U20)+TIMEVALUE("01:00"))*24,IF(Y20="DST",((F20+W20)-(D20+U20)-TIMEVALUE("01:00"))*24,((F20+W20)-(D20+U20))*24))

Unfortunately not working.
I ran formula evaluation but could not pick up an issue.
Attached mini sheet with results.

GG 24-25 C&S financials.xlsx
	D	E	F	G														U	V	W	X	Y	Z	AA
13		Date
14	Start		End															Depart		Arrive		Time Zone		[hh]:mm Duration
15
16
17
18
19	Thu 21-Nov-24		Thu 21-Nov-24															07:20		10:40		EST		104:00
20	Thu 21-Nov-24		Fri 22-Nov-24															08:00		10:00				624:00
21	Fri 22-Nov-24		Sat 23-Nov-24															08:00		07:00				552:00
22	Sun 24-Nov-24		Tue 26-Nov-24															20:00		07:00				840:00
23	Wed 27-Nov-24		Fri 29-Nov-24															20:00		07:00		DST		816:00
Blank (6)

Cell Formulas
Range	Formula
AA19:AA23	AA19	=IF(Y19="EST",((F19+W19)-(D19+U19)+TIMEVALUE("01:00"))*24,IF(Y19="DST",((F19+W19)-(D19+U19)-TIMEVALUE("01:00"))*24,((F19+W19)-(D19+U19))*24))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AA19:AB28	Cell Value	=0	text	YES

 

[ozbeachbum]

Book1
	D	E	F	G														U	V	W	X	Y	Z	AA
13		Date
14	Start		End															Depart		Arrive		Time Zone		[hh]:mm Duration
15
16
17
18
19	21-11-24		21-11-24															7:20		10:40		EST		04:20
20	21-11-24		22-11-24															8:00		10:00				26:00
21	22-11-24		23-11-24															8:00		7:00				23:00
22	24-11-24		26-11-24															20:00		7:00				35:00
23	27-11-24		29-11-24															20:00		7:00		DST		34:00
Sheet1

Cell Formulas
Range	Formula
AA19:AA23	AA19	=F19+W19-D19-U19+IF(Y19="EST",1,IF(Y19="DST",-1,0))*1/24

 

[ozbeachbum]

Thanks Bebo,
Tried to reply via your post, it put in your mini sheet and would not let me enter any text.
Works perfectly.
Cheers,
Dave.

 

[ozbeachbum]

Unfortunately not working.
I ran formula evaluation but could not pick up an issue.
Attached mini sheet with results.

GG 24-25 C&S financials.xlsx
	D	E	F	G														U	V	W	X	Y	Z	AA
13		Date
14	Start		End															Depart		Arrive		Time Zone		[hh]:mm Duration
15
16
17
18
19	Thu 21-Nov-24		Thu 21-Nov-24															07:20		10:40		EST		104:00
20	Thu 21-Nov-24		Fri 22-Nov-24															08:00		10:00				624:00
21	Fri 22-Nov-24		Sat 23-Nov-24															08:00		07:00				552:00
22	Sun 24-Nov-24		Tue 26-Nov-24															20:00		07:00				840:00
23	Wed 27-Nov-24		Fri 29-Nov-24															20:00		07:00		DST		816:00
Blank (6)

Cell Formulas
Range	Formula
AA19:AA23	AA19	=IF(Y19="EST",((F19+W19)-(D19+U19)+TIMEVALUE("01:00"))*24,IF(Y19="DST",((F19+W19)-(D19+U19)-TIMEVALUE("01:00"))*24,((F19+W19)-(D19+U19))*24))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AA19:AB28	Cell Value	=0	text	YES

Thanks again for your help Murray, as you will most likely see Bebo's formula works.
Cheers,
Dave.

 

[myall_blues]

You probably just need to remove all the '*24' in the formula because of your formatting, but you have a solution now so all good.