Time Tracking problem.

[Dow]

I have a time tracking report run by a program other than Excel. I
copy the data and paste it into Excel.

What I get is the amount of time spent in various conditions.
Simplified to 4 columns for this example, A B C D:

27:57:40 :03:13 19:38:04 1:48:57

All of these end up in different formats.
[h]:mm:ss - 1/1/1900 3:57:40 AM for the data in A1
General - :03:13 in B1
hh:mm:ss - 7:38:04 PM in C1
General - 1:48:57 in D1

First things first - it is the program I am copying from that uses the
format :00:00 for anything that has a 0 in the hour location.

How can I get a 0 into that hour location to display 0:03:13 for my
example?

This is the formula that I came up with:
IF(LEFT(B1,SEARCH(":",B1))=":","0"&B1,B1.

This works if true, but it fails to display B1 if false. I have to
use the TEXT formula on B1 to make it display correctly if false. I
can do that but I would have to insert 2 columns for each column of
data so I could do the TEXT Formula and then the IF formula. All this
I can do in VB.

Am I missing a simpler process/formula/VB solution for this?

So we get those numbers fixed. Now in my example I can take A1 and
subtract B1. I can also subtract C1 because it is already in a
compatible format. D1 on the other hand is in General format and even
when I try changing the format manually to [h]:mm:ss I cannot subtract
it from A1. I do not understand why it will not work.

Any thoughts here?

Ultimately the data that I want is A1 -B1 -C1 -D1. I considered
changing everything to decimal but I still run into some of the same
problems.

Please let me know any insights you might have. I am sure I am
missing something simple.

Thank you for the help.

 

[ryguy7272]

="0"&TEXT(A1,"hh:mm:ss")
or
="00"&TEXT(A1,"hh:mm:ss")

Then take the result and format as such:
=TEXT(A2-A1,"h:mm:ss")
or
=(MOD(A2-A1,1)*3600)/3600

That will show the number of minutes elapsed.
Does that work for you?

HTH,
Ryan---

 

[jasontferrell]

Am I missing a simpler process/formula/VB solution for this?
I think what could be happening is that the format for the cell with
the formula is set for general. When it displays the text of the
result, it looks okay, but it's just text. Maybe try setting the
format to an h:mm:ss format and use this formula:
=IF(ISNONTEXT(B1),B1,IF(LEFT(B1,1)=":",TIMEVALUE("0"&B1),B1))

So we get those numbers fixed.  Now in my example I can take A1 and
subtract B1.  I can also subtract C1 because it is already in a
compatible format.  D1 on the other hand is in General format and even
when I try changing the format manually to [h]:mm:ss I cannot subtract
it from A1.  I do not understand why it will not work.

Any thoughts here?

Try using the =Timevalue(D1) function.

 

[Dow]

Unfortunately that did not work. I tried it and when FALSE it still
displays the # VALUE error instead of the contents of cell B1. Looks
like I will have to insert 2 columns, do the TEXT first and then do
the IF statement.

D1 is still formatted in such a way that it is not compatible with A1
so I cannot go A1-D1. Manual formatting does not impact it.

 

[Dow]

I did not think of using ISNONTEXT. That works.

I tried the Timevalue before I posted. I found it in one of the other
Threads. Unfortunately I get a #Value error when I try it.

Am I missing a simpler process/formula/VB solution for this?

I think what could be happening is that the format for the cell with
the formula is set for general.  When it displays the text of the
result, it looks okay, but it's just text.  Maybe try setting the
format to an h:mm:ss format and use this formula:
=IF(ISNONTEXT(B1),B1,IF(LEFT(B1,1)=":",TIMEVALUE("0"&B1),B1))

So we get those numbers fixed.  Now in my example I can take A1 and
subtract B1.  I can also subtract C1 because it is already in a
compatible format.  D1 on the other hand is in General format and even
when I try changing the format manually to [h]:mm:ss I cannot subtract
it from A1.  I do not understand why it will not work.

Any thoughts here?

Try using the =Timevalue(D1) function.

 

[Dow]

Did some work on it and found a solution that works for all the
contingencies I was running into. Used all the suggestions I got and
was able to work it out. Thank you again everyone who helped.

=IF(ISNONTEXT(A1),A1,TIMEVALUE(TEXT(IF(LEFT(A1,SEARCH(":",A1,1))
=":","0"&A1,A1),"[h]:mm:ss")))

I did not think of using ISNONTEXT.  That works.

I tried the Timevalue before I posted.  I found it in one of the other
Threads.  Unfortunately I get a #Value error when I try it.

I think what could be happening is that the format for the cell with
the formula is set for general.  When it displays the text of the
result, it looks okay, but it's just text.  Maybe try setting the
format to an h:mm:ss format and use this formula:
=IF(ISNONTEXT(B1),B1,IF(LEFT(B1,1)=":",TIMEVALUE("0"&B1),B1))

So we get those numbers fixed.  Now in my example I can take A1 and
subtract B1.  I can also subtract C1 because it is already in a
compatible format.  D1 on the other hand is in General format and even
when I try changing the format manually to [h]:mm:ss I cannot subtract
it from A1.  I do not understand why it will not work.
Any thoughts here?

Try using the =Timevalue(D1) function.- Hide quoted text -

- Show quoted text -