dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
788
share rss forum feed


bjf123
We Want... A Shrubbery
Premium
join:2000-02-11
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.



mjhouser
Premium
join:2001-10-06

If you need a simple convert.




[=0.0416551] [m]"m";[1]h"h" m"m";h"h" m"m"


H2OuUp2
Happy to be here
Premium
join:2002-03-15
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:

=(EndTime-StartTime)/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



Kalford
Seems To Be An Rtfm Problem.
Premium,MVM
join:2001-03-20
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.



bjf123
We Want... A Shrubbery
Premium
join:2000-02-11
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.
=(EndTime-StartTime)/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.



H2OuUp2
Happy to be here
Premium
join:2002-03-15
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 *

=(EndTime-StartTime)*24

Sorry
--
He is no fool who gives up what he cannot keep, to gain what he cannot lose. - Jim Elliot



bjf123
We Want... A Shrubbery
Premium
join:2000-02-11
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.



bjf123
We Want... A Shrubbery
Premium
join:2000-02-11
Hamilton, OH

Think I might have it. I put =(B12-A12)*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.



mjhouser
Premium
join:2001-10-06
reply to bjf123

Some good info here - »support.microsoft.com/kb/214094