More Excel Answers
Question Library
Ask a question about Excel
Volunteer
Experts of the Month
Expert Login
Awards
About Us
Tell friends
Link to Us
Disclaimer
|
| |
|
|
| |
| | | |
About Tom Ogilvy
Expertise
Worked with the program for many years - provided assistance on MS Excel Newsgroups since 1997. Have received the Microsoft MVP award annually since 1999.
I don't answer questions on using Excel in a browser
Since I have no way to test this. Prefer not to answer charting questions. I consider myself to be particularly knowledgeable about using VBA internal to Excel but have no problems with formulas and pivot tables either.
Experience
Have Used Excel for 15 - 20 years. Answered in excess of 70,000 Excel related questions in MS Excel newsgroups. Unless obvious, please specify whether you want a worksheet function or macro/VBA solution.
Education/Credentials
BS General Engineering (concentration in Industrial Engineering)
MS Operations Research Systems Analysis
| | |
| |
You are here: Experts > Computing/Technology > Microsoft Software > Excel > time calculation
Excel - time calculation
Expert: Tom Ogilvy - 11/7/2009
Question
Hi Tom,
I have a large spreadsheet with production times, ie time job started and time job finished. Unfortunately, all of the data has been keyed in decimal rather than a time format. I now need to add a column and calculate the time a job has taken. For example.
Time started Time Finished Time Taken
8.55 11.20 2.25
I have looked at the TRUNK and RIGHT functions but have been unable to calculate the "Time Taken" as the time has is in decimal and converting decimal to a time format gives a strange result.
Is there any way the start times and finish times can be converted into "minutes" then subtracted from each other and then converted back into hours and minutes for the "Time Taken".
In the above example, converting to minutes would result in 535 and 680 minutes respectively. So the time taken to do the job would be 145 minutes or 2 hours 25 minutes.
I would be most grateful if you could help with this one.
Kind Regards
Malcolm
Answer
Malcom,
E2: 8.55
E3: 11.2
E4: =SUBSTITUTE(TEXT(SUBSTITUTE(TEXT(E3,"#.00"),".",":")-SUBSTITUTE(TEXT(E2,"#.00"),".",":"),"h:mm"),":",".")*1
produces 2.25
for me.
Basically, use substitute to replace the period with a colon, then subtract two time values, then convert the answer back to a decimal representation.
--
Regards,
Tom Ogilvy
Ask a Question
|
|
