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

O

ozbeachbum

Board Regular
Joined
Jun 3, 2015
Messages
221
Office Version
  1. 2021
Platform
  1. Windows
=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
DEFGUVWXYZAAABACAD
13Date
14StartEndDepartArriveTime Zone[hh]:mm Duration
15
16
17
18
19Thu 21-Nov-24Thu 21-Nov-2407:2010:40EST04:20
20Thu 21-Nov-24Fri 22-Nov-2408:0010:0026:00CORRECT26:00
21Fri 22-Nov-24Sat 23-Nov-2408:0007:0023:00CORRECT23:00
22Sun 24-Nov-24Tue 26-Nov-2420:0007:0035:00CORRECT35:00
23Wed 27-Nov-24Fri 29-Nov-2420:0007:00DST##########Would need to be34:00
Blank (5)
Cell Formulas
RangeFormula
AC20:AC23AC20=IF(AA20=AD20, "CORRECT","Would need to be")
AD20:AD21AD20=W20-U20+DAY(1)
AD22AD22=W22-U22+DAY(2)
AD23AD23=(W23-U23-TIMEVALUE("01:00"))+DAY(2)
AA19:AA23AA19=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
CellConditionCell FormatStop If True
AA19:AB28Cell Value=0textYES
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
What about this Dave?
Excel Formula: 
=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))
 
Upvote 0
Book1
DEFGUVWXYZAA
13Date
14StartEndDepartArriveTime Zone[hh]:mm Duration
15
16
17
18
1921-11-2421-11-247:2010:40EST04:20
2021-11-2422-11-248:0010:0026:00
2122-11-2423-11-248:007:0023:00
2224-11-2426-11-2420:007:0035:00
2327-11-2429-11-2420:007:00DST34:00
Sheet1
Cell Formulas
RangeFormula
AA19:AA23AA19=F19+W19-D19-U19+IF(Y19="EST",1,IF(Y19="DST",-1,0))*1/24
 
Upvote 0
Solution
myall_blues said:
What about this Dave?
Excel Formula: 
=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))
Click to expand...
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
DEFGUVWXYZAA
13Date
14StartEndDepartArriveTime Zone[hh]:mm Duration
15
16
17
18
19Thu 21-Nov-24Thu 21-Nov-2407:2010:40EST104:00
20Thu 21-Nov-24Fri 22-Nov-2408:0010:00624:00
21Fri 22-Nov-24Sat 23-Nov-2408:0007:00552:00
22Sun 24-Nov-24Tue 26-Nov-2420:0007:00840:00
23Wed 27-Nov-24Fri 29-Nov-2420:0007:00DST816:00
Blank (6)
Cell Formulas
RangeFormula
AA19:AA23AA19=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
CellConditionCell FormatStop If True
AA19:AB28Cell Value=0textYES
 
Upvote 0
bebo021999 said:
Book1
DEFGUVWXYZAA
13Date
14StartEndDepartArriveTime Zone[hh]:mm Duration
15
16
17
18
1921-11-2421-11-247:2010:40EST04:20
2021-11-2422-11-248:0010:0026:00
2122-11-2423-11-248:007:0023:00
2224-11-2426-11-2420:007:0035:00
2327-11-2429-11-2420:007:00DST34:00
Sheet1
Cell Formulas
RangeFormula
AA19:AA23AA19=F19+W19-D19-U19+IF(Y19="EST",1,IF(Y19="DST",-1,0))*1/24
Click to expand...
 
Upvote 0
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.
 
Upvote 0
ozbeachbum said:
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
DEFGUVWXYZAA
13Date
14StartEndDepartArriveTime Zone[hh]:mm Duration
15
16
17
18
19Thu 21-Nov-24Thu 21-Nov-2407:2010:40EST104:00
20Thu 21-Nov-24Fri 22-Nov-2408:0010:00624:00
21Fri 22-Nov-24Sat 23-Nov-2408:0007:00552:00
22Sun 24-Nov-24Tue 26-Nov-2420:0007:00840:00
23Wed 27-Nov-24Fri 29-Nov-2420:0007:00DST816:00
Blank (6)
Cell Formulas
RangeFormula
AA19:AA23AA19=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
CellConditionCell FormatStop If True
AA19:AB28Cell Value=0textYES
Click to expand...
Thanks again for your help Murray, as you will most likely see Bebo's formula works.
Cheers,
Dave.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
849
paulkc
P
E
  • Solved
VBA Find match, if found, copy rows and paste in different workbook
Replies
6
Views
461
MARK858
MARK858
S
  • Solved
Trying to Get a button to auto populate cells in a budget calendar I created
Replies
2
Views
961
scottyb1977
S

Forum statistics

Threads
1,223,101
Messages
6,170,116
Members
452,302
Latest member
TaMere

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top