Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Tracking problem.
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Tracking problem.
="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--- -- RyGuy "Dow" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Tracking problem.
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Tracking problem.
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. On Mar 17, 1:47*pm, ryguy7272 wrote: ="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--- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Tracking problem.
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. On Mar 17, 1:55*pm, jasontferrell wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Tracking problem.
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,SEA RCH(":",A1,1)) =":","0"&A1,A1),"[h]:mm:ss"))) On Mar 18, 12:42*pm, Dow wrote: 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. On Mar 17, 1:55*pm, jasontferrell wrote: 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.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tracking Time of different city in comparision to Indian Standared Time | Excel Programming | |||
Tracking Time of different city in comparision to Indian Standared Time | Excel Programming | |||
Tracking Time | Excel Worksheet Functions | |||
Tracking Time | Excel Discussion (Misc queries) | |||
Time tracking | Excel Programming |