# VARIATION ON TRANSIT TIME/DAY

#### jimmy2468

##### New Member
Thanks for the last reply!

Another one...

IF I have:
"Monday" (departure day) in cell A1
"17:00" (departure time) in cell B1
"49" in cell C1 (travel time) in cell C1

How could I get:
"Wed-18:00" (arrival day and arrival time) or something similar to output in cell D1?

Really appreciate any assistance....

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### erik.van.geit

##### MrExcel MVP
Hi, jimmy2468
Welcome to the Board !!!!

it's not sure the members who answered your first question would see your "thanks" here

your request is somehow problematic - at least for me - since there seems not to be a conversionformula like "dayname-to-daynumber"

so I would suggest to use a lookup conversion table

- EDIT: deleted example + formulas: would be too confusing for future reference -
see Barries post, which does it all-in-one-cell

kind regards,
Erik

#### erik.van.geit

##### MrExcel MVP
this would be an easier request
instead of "name of day", using a date

A       B     C           D        E
1 date    hour  added hours new date new hour
2 2/02/05 13:00 14:21       3/02/05  3:21

time1

[Table-It] version 06 by Erik Van Geit
Code:
``````RANGE FORMULA (1st cell)
D2    =DATE(YEAR(A2),MONTH(A2),DAY(A2)+INT(B2+C2))
E2    =MOD((B2+C2),1)

[Table-It] version 06 by Erik Van Geit``````

#### barry houdini

##### MrExcel MVP
Hi Jimmy,

try this formula in D1

=TEXT(MATCH(LEFT(A1,2),{"Su","Mo","Tu","We","Th","Fr","Sa"},0)+B1+C1/24,"ddd-hh:mm")

#### jimmy2468

##### New Member
Wow. Thank you both! Saved me a bunch of time!!

#### jimmy2468

##### New Member
How would I modify

=TEXT(MATCH(LEFT(A1,2),{"Su","Mo","Tu","We","Th","Fr","Sa"},0)+B1+C1/24,"ddd-hh:mm")

to subtract from the arrival time That is:
The output is: Tue-10:00, but because of a time-zone change, the output should read Tue-6:00

Thanks!

#### erik.van.geit

##### MrExcel MVP
How would I modify

=TEXT(MATCH(LEFT(A1,2),{"Su","Mo","Tu","We","Th","Fr","Sa"},0)+B1+C1/24,"ddd-hh:mm")

to subtract from the arrival time That is:
The output is: Tue-10:00, but because of a time-zone change, the output should read Tue-6:00

Thanks!
you will need to "adjust" the time using an extra cell: let's use D1 to put the timedifference (this cell could contain a formula to compare the timezones ...)

instead of C1/24 you need (C1 + D1)/24
D1 is positive when flying to the east and negative flying to the west

=TEXT(MATCH(LEFT(A1,2),{"Su","Mo","Tu","We","Th","Fr","Sa"},0)+B1+(C1+D1)/24,"ddd-hh:mm")

best regards,
Erik

Replies
12
Views
643
Replies
0
Views
603
Replies
3
Views
517
Replies
3
Views
397
Replies
3
Views
229

1,171,179
Messages
5,874,192
Members
433,033
Latest member
thatmacroguy

### 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?

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