i converted a pdf to excel now i am trying to add hours from the text box but gives me error #value!
adding 18339:29 & 18400:42
when i click on show calculation steps it shows ("18400:42" +"18339:29")
i converted a pdf to excel now i am trying to add hours from the text box but gives me error #value!
adding 18339:29 & 18400:42
when i click on show calculation steps it shows ("18400:42" +"18339:29")
Try this...
In A1 Cell
18339:29
In A2 Cell
18400:42
In A3 Cell
=SUMPRODUCT(1*LEFT(A1:A2,FIND(":",A1:A2)-1))+INT(SUMPRODUCT(1*RIGHT(A1:A2,2))/60)&":"&TEXT(MOD(SUMPRODUCT(1*RIGHT(A1:A2,2)),60),"00")
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Thank you very much sir! worked like a charm.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
HI,
what if its for individual cells, lets say G2 and N2, Can you please provide a forumla ?
Last edited by pannam; 01-28-2014 at 01:00 AM. Reason: deleted quote
Just adapt the ranges for single cells alone
Please click Edit on Post #5 and remove my quoted sentence.
Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.
As an alternative i used this as a module,
=================================================
Option Explicit
Function SubtrTimes(CurrentTime As Range, LastTime As Range) As String
Dim v1 As Variant, v2 As Variant
Dim t1 As Long, t2 As Long
Dim temp As Double
Dim hrs As Long, mins As Long
v1 = Split(CurrentTime.Text, ":")
v2 = Split(LastTime.Text, ":")
t1 = v1(0) - v2(0)
t2 = v1(1) - v2(1)
temp = t1 + t2 / 60
hrs = Int(temp)
mins = (temp - Int(temp)) * 60
SubtrTimes = Format(hrs, "#0") & Format(mins, "\:00")
End Function
==============================
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks