#1   Report Post  
Posted to microsoft.public.excel.misc
Art Art is offline
external usenet poster
 
Posts: 587
Default Elapse Time

I would to calculate the elapsed time between the start and end
Time, to the nearest second. So that I can generate a chart to graphically
show what day the job took longer to process.

Example:
Date: start end
Monday 11:00 12:33
Tuesday 11:05 12:05
Wednesday 11:10 11:30

Any suggestion would be appreciated.

  #2   Report Post  
Posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default Elapse Time

In D1 put ......ElapsedTime
In D3 put.......=C2-B2 and copy this formula down column D

hth
Vaya con Dios,
Chuck, CABGx3



"Art" wrote:

I would to calculate the elapsed time between the start and end
Time, to the nearest second. So that I can generate a chart to graphically
show what day the job took longer to process.

Example:
Date: start end
Monday 11:00 12:33
Tuesday 11:05 12:05
Wednesday 11:10 11:30

Any suggestion would be appreciated.

  #3   Report Post  
Posted to microsoft.public.excel.misc
Art Art is offline
external usenet poster
 
Posts: 587
Default Elapse Time

Chuck, Would it be possible to change 1.86 to 2.26 ( hh:mm ).

(End Time) 9.24 - (Start Time) 7.38 = (Elapsed Time) 1.86

So that when the chart is displayed the folks don't have to the math.

Really appeciate..


"CLR" wrote:

In D1 put ......ElapsedTime
In D3 put.......=C2-B2 and copy this formula down column D

hth
Vaya con Dios,
Chuck, CABGx3



"Art" wrote:

I would to calculate the elapsed time between the start and end
Time, to the nearest second. So that I can generate a chart to graphically
show what day the job took longer to process.

Example:
Date: start end
Monday 11:00 12:33
Tuesday 11:05 12:05
Wednesday 11:10 11:30

Any suggestion would be appreciated.

  #4   Report Post  
Posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default Elapse Time

With EndTime in C2 and StartTime in B2....

=INT(C2-B2)+INT((C2-B2-INT(C2-B2))*100/60)+(((C2-B2-INT(C2-B2))*100/60-INT((C2-B2-INT(C2-B2))*100/60))*60)/100

All in one cell, watch out for word-wrap

Vaya con Dios,
Chuck, CABGx3



"Art" wrote:

Chuck, Would it be possible to change 1.86 to 2.26 ( hh:mm ).

(End Time) 9.24 - (Start Time) 7.38 = (Elapsed Time) 1.86

So that when the chart is displayed the folks don't have to the math.

Really appeciate..


"CLR" wrote:

In D1 put ......ElapsedTime
In D3 put.......=C2-B2 and copy this formula down column D

hth
Vaya con Dios,
Chuck, CABGx3



"Art" wrote:

I would to calculate the elapsed time between the start and end
Time, to the nearest second. So that I can generate a chart to graphically
show what day the job took longer to process.

Example:
Date: start end
Monday 11:00 12:33
Tuesday 11:05 12:05
Wednesday 11:10 11:30

Any suggestion would be appreciated.

  #5   Report Post  
Posted to microsoft.public.excel.misc
Art Art is offline
external usenet poster
 
Posts: 587
Default Elapse Time

Thanks Chuck that worked..

"CLR" wrote:

With EndTime in C2 and StartTime in B2....

=INT(C2-B2)+INT((C2-B2-INT(C2-B2))*100/60)+(((C2-B2-INT(C2-B2))*100/60-INT((C2-B2-INT(C2-B2))*100/60))*60)/100

All in one cell, watch out for word-wrap

Vaya con Dios,
Chuck, CABGx3



"Art" wrote:

Chuck, Would it be possible to change 1.86 to 2.26 ( hh:mm ).

(End Time) 9.24 - (Start Time) 7.38 = (Elapsed Time) 1.86

So that when the chart is displayed the folks don't have to the math.

Really appeciate..


"CLR" wrote:

In D1 put ......ElapsedTime
In D3 put.......=C2-B2 and copy this formula down column D

hth
Vaya con Dios,
Chuck, CABGx3



"Art" wrote:

I would to calculate the elapsed time between the start and end
Time, to the nearest second. So that I can generate a chart to graphically
show what day the job took longer to process.

Example:
Date: start end
Monday 11:00 12:33
Tuesday 11:05 12:05
Wednesday 11:10 11:30

Any suggestion would be appreciated.



  #6   Report Post  
Posted to microsoft.public.excel.misc
Art Art is offline
external usenet poster
 
Posts: 587
Default Elapse Time

Chuck, sorry for the inconvenience but hopefully these question will help
others too.

Your formula worked fine till I had to enter the time after midnight.

(Start Time) 22.42 €“ (End time) 00.59 = ( Elapsed time) -21.83

How can I get the -21.83 to 2.17 ?


I appreciate your Help..


"CLR" wrote:

With EndTime in C2 and StartTime in B2....

=INT(C2-B2)+INT((C2-B2-INT(C2-B2))*100/60)+(((C2-B2-INT(C2-B2))*100/60-INT((C2-B2-INT(C2-B2))*100/60))*60)/100

All in one cell, watch out for word-wrap

Vaya con Dios,
Chuck, CABGx3



"Art" wrote:

Chuck, Would it be possible to change 1.86 to 2.26 ( hh:mm ).

(End Time) 9.24 - (Start Time) 7.38 = (Elapsed Time) 1.86

So that when the chart is displayed the folks don't have to the math.

Really appeciate..


"CLR" wrote:

In D1 put ......ElapsedTime
In D3 put.......=C2-B2 and copy this formula down column D

hth
Vaya con Dios,
Chuck, CABGx3



"Art" wrote:

I would to calculate the elapsed time between the start and end
Time, to the nearest second. So that I can generate a chart to graphically
show what day the job took longer to process.

Example:
Date: start end
Monday 11:00 12:33
Tuesday 11:05 12:05
Wednesday 11:10 11:30

Any suggestion would be appreciated.

  #7   Report Post  
Posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default Elapse Time

You can't solve that problem properly unless you use DATES as well as times.
See Chip's page for much more info about working with TIMES.........

http://www.cpearson.com/excel/datetime.htm#AddingTimes

hth
Vaya con Dios,
Chuck, CABGx3



"Art" wrote:

Chuck, sorry for the inconvenience but hopefully these question will help
others too.

Your formula worked fine till I had to enter the time after midnight.

(Start Time) 22.42 €“ (End time) 00.59 = ( Elapsed time) -21.83

How can I get the -21.83 to 2.17 ?


I appreciate your Help..


"CLR" wrote:

With EndTime in C2 and StartTime in B2....

=INT(C2-B2)+INT((C2-B2-INT(C2-B2))*100/60)+(((C2-B2-INT(C2-B2))*100/60-INT((C2-B2-INT(C2-B2))*100/60))*60)/100

All in one cell, watch out for word-wrap

Vaya con Dios,
Chuck, CABGx3



"Art" wrote:

Chuck, Would it be possible to change 1.86 to 2.26 ( hh:mm ).

(End Time) 9.24 - (Start Time) 7.38 = (Elapsed Time) 1.86

So that when the chart is displayed the folks don't have to the math.

Really appeciate..


"CLR" wrote:

In D1 put ......ElapsedTime
In D3 put.......=C2-B2 and copy this formula down column D

hth
Vaya con Dios,
Chuck, CABGx3



"Art" wrote:

I would to calculate the elapsed time between the start and end
Time, to the nearest second. So that I can generate a chart to graphically
show what day the job took longer to process.

Example:
Date: start end
Monday 11:00 12:33
Tuesday 11:05 12:05
Wednesday 11:10 11:30

Any suggestion would be appreciated.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
synchronizing timestamp feature with time on video software. 999 Excel Discussion (Misc queries) 4 July 30th 06 10:49 PM
formula to determine time range overlap? William DeLeo Excel Discussion (Misc queries) 0 June 6th 06 08:26 PM
Hot key for time? Dave in Des Moines Excel Worksheet Functions 2 March 24th 06 05:15 PM
time sheet drop down lists Steve Excel Discussion (Misc queries) 12 March 18th 06 11:30 PM
time differences in a column 68magnolia71 Excel Worksheet Functions 3 May 9th 05 09:46 PM


All times are GMT +1. The time now is 08:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"