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

O

ozbeachbum

Board Regular
Joined
Jun 3, 2015
Messages
233
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
898
paulkc
P
E
  • Solved
VBA Find match, if found, copy rows and paste in different workbook
Replies
6
Views
481
MARK858
MARK858
S
  • Solved
Trying to Get a button to auto populate cells in a budget calendar I created
Replies
2
Views
992
scottyb1977
S

Forum statistics

Threads
1,223,879
Messages
6,175,145
Members
452,615
Latest member
bogeys2birdies

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