subtract hours
Hello,
I maybe have a simple question.
There are Machine hours wich just get higher. no date or time.
64510 h 41 Min
64522 h 26 Min
no dezimal minutes.
the result should be in hh:mm if possible.
If i try to substract the first from the second there are Errors like Value
I hope someone can help me.
Greetings
Dear Akoya, In excel :
if 64510 mean 2687.92 (hh:mm)Formula:
Please Login or Register to view this content.
if you convert minutes :Formula:Please Login or Register to view this content.
if you convert secons :Formula:Please Login or Register to view this content.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
Try this formula and format has time
Formula:Please Login or Register to view this content.
where A1 is 64510:41:00 and A2 is 64522:26:00.
Assuming those values are in A1 and A2, you can use this formula to get the difference in Excel time format:
=(LEFT(A2,FIND(" ",A2))-LEFT(A1,FIND(" ",A1)))/24 + (MID(A2,SEARCH("h",A2)+1,3)-MID(A1,SEARCH("h",A1)+1,3))/24/60
Format as [h]:mm
Note: you may need to use a semicolon ( ; ) instead of a comma ( , ) and to translate the function names into German, depending on your regional settings.
Hope this helps.
Pete
The attached file shows this working.
Hope this helps.
Pete
It appears you can enter a time up to 9999:59:59 but when you enter 10000:00:00 it changes to text (left justified)
If you enter 9999:59:59, then F2 to edit and hit enter it reformats the cell to date hh:mm:ss
If you do the same with 10000:00:00 it automatically left justifies the value.
Hitting F2 and enter does not change the value
=TRIM(MID(SUBSTITUTE(H1," ",REPT(" ",50)),1,50)) provides hours
=TRIM(MID(SUBSTITUTE(H1," ",REPT(" ",50)),100,50)) provides minutes
Just do a MOD(...,24) on the result you get from the hours to give you the hours into the day.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
I got the impression that the values were exactly as stated, i.e. 64510 h 41 Min, in text format.
Pete
Dear Akoya, kindly confirm exact result of "64510 h 41 Min" & "64522 h 26 Min" (in exact hh:mm)Originally Posted by Akoya1992
Thank you for yout help an quick respond!
It's not clear who you are responding to, but if you have an answer to your question please take a moment to select Thread Tools on the menu above your first post and mark this thread as SOLVED.
Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks