bjf123We Want... A ShrubberyPremium join:20000211 Hamilton, OH  [Excel] Time Calculation We want to start tracking some equipment productivity. The monitoring system can generate data showing when an operation was started and when it ended, e.g. start 8:00AM end 10:10AM, time elapsed 2:10. However, when we export the productivity report to Excel, the 2:10 is shown as 2:10AM, not 2 hours 10 minutes, or even 130 minutes. I haven't seen the report, but have been asked if I've got any ideas on how to convert it. I've never had much need to work with time calculations in Excel, so I'm hoping someone here can help. Help!  Golf is a relatively simple game, played by reasonably intelligent people, stupidly. 


 If you need a simple convert.
[=0.0416551] [m]"m";[1]h"h" m"m";h"h" m"m" 

H2OuUp2Happy to be herePremium join:20020315 Oklahoma City, OK 1 edit  reply to bjf123
If you are having to calculate things you will want to convert to hundredths. Do this this enter this formula:
=(EndTimeStartTime)/24 then change the formatting to numbers 2 decimal places.
 He is no fool who gives up what he cannot keep, to gain what he cannot lose.  Jim Elliot 

KalfordSeems To Be An Rtfm Problem.Premium,MVM join:20010320 Ontario kudos:1  reply to bjf123
One challenge you might run into with respect to how it is coming into excel would be how the original program's calculations treat durations that are greater than 24 hours and/or span across two dates ie. 11pm to 1am.
Setting that aside. . . the most hassle free way I find to manipulate date and time serial** imported into excel is by converting them to a text string using my own format  in this case one that first strips off the AM/PM, next we extract out the hours and minutes and do the conversion to minutes. (assumed valid data range  from a duration of 0 minutes {12:00AM}  to a maximum 1439 minutes {11:59PM}  otherwise excel would return value errors)
For the following example cell H2 will contain your 2:10AM "timestamp" value. This actual represents a duration of 2 hours and 10 minutes  or 130 if converted to minutes.
In cell I2 place the formula
=TEXT(H2,"h:mm")
the displayed results of I2 should be
2:10
In cell J2 formula would be
=LEFT(I2,FIND(":",I2,1)1)*60+RIGHT(I2,2))
contents would be 130
(what this formula does is get the hour from everything the left of the colon and multiplies it by 60. It then adds minutes which are the right two characters of cell I2)
** note: if your 2:10AM comes into excel as a text string, then the above method will not work. The results you desire can still be accomplished  you just have insert an extra step that tests for PM (add 12 hours to duration if true), as well as using different combinations of functions to pull out the hours and minutes.


bjf123We Want... A ShrubberyPremium join:20000211 Hamilton, OH  reply to bjf123
I'll have to find out about how the system handles things going from one day to the next. We do operate three shifts, so that will be a factor.
I can't seem to get this formula to work. =(EndTimeStartTime)/24 then change the formatting to numbers 2 decimal places. If I put in a start of 8:00 and an end time of 10:00, I get a result of 0.00347.
The other two do work, except when the time crosses over midnight. Going to have to play around with that.
 Golf is a relatively simple game, played by reasonably intelligent people, stupidly. 

H2OuUp2Happy to be herePremium join:20020315 Oklahoma City, OK  reply to bjf123
I guess if I would put the right formula it would help. It was late when I did it and put / instead of *
=(EndTimeStartTime)*24
Sorry  He is no fool who gives up what he cannot keep, to gain what he cannot lose.  Jim Elliot 

bjf123We Want... A ShrubberyPremium join:20000211 Hamilton, OH  Thanks. That works. Still got to figure out how to handle the roll over past midnight.  Golf is a relatively simple game, played by reasonably intelligent people, stupidly. 

bjf123We Want... A ShrubberyPremium join:20000211 Hamilton, OH  Think I might have it. I put =(B12A12)*24 inside an =if argument and added that if b12 is less than a12 to add 24 hours to the elapsed time. Without that a start time of 11PM and an end time of 1AM returns minus 22 hours. With that if statement in there, it now returns 2 hours. I still need to see how the data comes out of the system. If it's military time, this will work. If it's AM and PM, I'll need to do more work.  Golf is a relatively simple game, played by reasonably intelligent people, stupidly. 

 reply to bjf123
Some good info here  »support.microsoft.com/kb/214094 
