VARIATION ON TRANSIT TIME/DAY

[jimmy2468]

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....

 

[erik.van.geit]

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]

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

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]

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]

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

 

[jimmy2468]

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]

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