calculate time
- Thread starter BillS
- Start date
NARAYANK991
Excel Ninja
Hi Bill ,
Can we take it that if the arriving day is after the departure day , then the week is the same , else the week is one week later ?
In the examples you have given , the first train leaves on Monday , runs throughout Tuesday , and arrives on Wednesday ; the second departs on Friday , runs throughout Saturday , Sunday , Monday , Tuesday , Wednesday , and arrives on Thursday. Is this correct ?
Narayan
Can we take it that if the arriving day is after the departure day , then the week is the same , else the week is one week later ?
In the examples you have given , the first train leaves on Monday , runs throughout Tuesday , and arrives on Wednesday ; the second departs on Friday , runs throughout Saturday , Sunday , Monday , Tuesday , Wednesday , and arrives on Thursday. Is this correct ?
Narayan
NARAYANK991
Excel Ninja
Hi Bill ,
If you can use helper cells , then do the following :
Suppose the Depart_Day is in one cell , the Depart_Time is in another cell ; similarly , the Arrive_Day is in a third cell , and the Arrive_Time is in a fourth cell ; then , the following formula ( Formula_1 ) :
=MIN(IF(TEXT(ROW(INDIRECT(TODAY()&":"&TODAY()+30)),"dddd")=Depart_Day,ROW(INDIRECT(TODAY()&":"&TODAY()+30))))+Depart_Time
entered as an array formula , gives a date + time corresponding to the departure day and time.
Similarly , the following formula ( Formula_2 ) :
=MIN(IF(TEXT(ROW(INDIRECT(INT(Formula_1)&":"&INT(Formula_1)+7)),"dddd")=Arrive_Day,ROW(INDIRECT(INT(Formula_1)&":"&INT(Formula_1)+7))))+Arrive_Time
entered as an array formula , gives a date + time corresponding to the arrival day and time.
Now the following formula :
=IF(Formula_2<=Formula_1,Formula_2+7-Formula_1,Formula_1)
gives the difference ; formatting the cell as [h]:mm:ss will give the difference in hours.
Narayan
If you can use helper cells , then do the following :
Suppose the Depart_Day is in one cell , the Depart_Time is in another cell ; similarly , the Arrive_Day is in a third cell , and the Arrive_Time is in a fourth cell ; then , the following formula ( Formula_1 ) :
=MIN(IF(TEXT(ROW(INDIRECT(TODAY()&":"&TODAY()+30)),"dddd")=Depart_Day,ROW(INDIRECT(TODAY()&":"&TODAY()+30))))+Depart_Time
entered as an array formula , gives a date + time corresponding to the departure day and time.
Similarly , the following formula ( Formula_2 ) :
=MIN(IF(TEXT(ROW(INDIRECT(INT(Formula_1)&":"&INT(Formula_1)+7)),"dddd")=Arrive_Day,ROW(INDIRECT(INT(Formula_1)&":"&INT(Formula_1)+7))))+Arrive_Time
entered as an array formula , gives a date + time corresponding to the arrival day and time.
Now the following formula :
=IF(Formula_2<=Formula_1,Formula_2+7-Formula_1,Formula_1)
gives the difference ; formatting the cell as [h]:mm:ss will give the difference in hours.
Narayan
Debraj
Excel Ninja
Hi Bills..
Create a Name for WeekName as
[/pre]
Regards,
Deb..
PS: sorry for the length..
Create a Name for WeekName as
Code:
{"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"}
or Use a separate are for Sunday..Monday..
then try below formula..
[pre]=TEXT(IF(MATCH(DepartDay,WeekName,0)<MATCH(ArriveDay,WeekName,0),
(TODAY()+MATCH(ArriveDay,WeekName,0)+ArriveTime)-
(TODAY()+MATCH(DepartDay,WeekName,0)+DepartTime),
(TODAY()+6+MATCH(DepartDay,WeekName,0)+DepartTime)-
(TODAY()+MATCH(ArriveDay,WeekName,0)+ArriveTime)),
"dd ""Days &"" hh:mm:ss")Regards,
Deb..
PS: sorry for the length..
Debraj
Excel Ninja
Hi BillS!
You can upload your file in any file sharing site.. (http://www.speedyshare.com/) or anywhere from Below post
http://chandoo.org/forums/topic/posting-a-sample-workbook
still if your data is secure then you can randomly change some ACTUAL data and upload a sample file also..
BTW.. you can send file directly to me also at mail3debraj[at]gmail[dot]com .. I will keep it secret between us.. before uploading, with answer
Regards,
Deb
You can upload your file in any file sharing site.. (http://www.speedyshare.com/) or anywhere from Below post
http://chandoo.org/forums/topic/posting-a-sample-workbook
still if your data is secure then you can randomly change some ACTUAL data and upload a sample file also..
BTW.. you can send file directly to me also at mail3debraj[at]gmail[dot]com .. I will keep it secret between us.. before uploading, with answer
Regards,
Deb
Debraj
Excel Ninja
Hi BillS...
Try below formula..
[pre]
[/pre]
https://dl.dropbox.com/u/78831150/Excel/Train%20Sched%28BillS%29.xlsx
PS: comments related to length of formula, without a shorter formula..
will be treated as SPAM..
Regards,
Deb
Try below formula..
[pre]
Code:
=TEXT(
(TODAY()-WEEKDAY(TODAY())+
IF(WEEKDAY(TODAY())>MATCH(ArriveDay,WeekName,0),
MATCH(ArriveDay,WeekName,0)+7,
MATCH(ArriveDay,WeekName,0))
+ArriveTime)
- (TODAY()-WEEKDAY(TODAY())+
IF(WEEKDAY(TODAY())>MATCH(DepartDay,WeekName,0),
MATCH(DepartDay,WeekName,0)+7,
MATCH(DepartDay,WeekName,0))
+DepartTime),
"dd ""Days &"" hh:mm:ss")https://dl.dropbox.com/u/78831150/Excel/Train%20Sched%28BillS%29.xlsx
PS: comments related to length of formula, without a shorter formula..
will be treated as SPAM..
Regards,
Deb
Hello Bills,
Consider these:
A1 : Departure Day
A2 : Arrival Day
B1 : Departure Time
B2 : Arrival Time
Then try this formula;
=MATCH(A2,TEXT(DATE(1904,1,MATCH(A1,TEXT(DATE(1904,1,{3,4,5,6,7,8,9}),"dddd"),0)+{3,4,5,6,7,8,9}),"dddd"),0)-1&" Days "&TEXT(B2+(B1>B2)-B1,"hh:mm:ss")
If you want to include Departure day also, remove -1.
Hope this helps,
Haseeb
Consider these:
A1 : Departure Day
A2 : Arrival Day
B1 : Departure Time
B2 : Arrival Time
Then try this formula;
=MATCH(A2,TEXT(DATE(1904,1,MATCH(A1,TEXT(DATE(1904,1,{3,4,5,6,7,8,9}),"dddd"),0)+{3,4,5,6,7,8,9}),"dddd"),0)-1&" Days "&TEXT(B2+(B1>B2)-B1,"hh:mm:ss")
If you want to include Departure day also, remove -1.
Hope this helps,
Haseeb