Customized time conversion help

[WhiteRaven76]

I have been trying to figure out how to create some VBA code that will convert time (hh:mm) to decimal (hours.units) using a specific time conversion chart.

I have been failing horribly at trying to make some VBA code. So far I have had success by trying to take the time from column AB (starting in AB2) and separate the Hours into column AD and Minutes into column AE. I have been able to separate the time using =LEFT and =RIGHT.

The next part is evading me. I need to take the minutes from column AE2 and have it scan AF2 through AF61. When it finds a match, it takes the data from the adjacent cell in column AG and merges that with the hours from column AD2 and puts that data into column AH2. Then it continues on to AE3 and AE4 until it reaches the end of column AE.

Every attempt I have made so far has failed horribly and I deleted it out of frustration. Any help or advice would be extremely appreciated!

 

[PeteWright]

Is this what you are looking for?

I have two formulas depending if your input is a TIME-value (cell A1) or TEXT (cell B1):

Book1.xlsm
	A	B
1	21:34	21.57
2	21:34	21.56667
Sheet3

Cell Formulas
Range	Formula
B1	B1	= A1 * 24
B2	B2	= NUMBERVALUE(LEFT(A2,2)) + NUMBERVALUE(RIGHT(A2,2))/60

 

[PeteWright]

Or in case you need VBA code, you could use this:

Public Function time2decimal(ByVal str As String) As Double
   time2decimal = CDbl(Left(str, 2)) + CDbl(Right(str, 2)) / 60
End Function

 

[WhiteRaven76]

Is this what you are looking for?

I have two formulas depending if your input is a TIME-value (cell A1) or TEXT (cell B1):

Book1.xlsm
	A	B
1	21:34	21.57
2	21:34	21.56667
Sheet3

Cell Formulas
Range	Formula
B1	B1	= A1 * 24
B2	B2	= NUMBERVALUE(LEFT(A2,2)) + NUMBERVALUE(RIGHT(A2,2))/60

Thanks for the help Pete!

I have a formula that converts time into decimal. Unfortunately, it doesn’t convert the same as a chart I am using. There are times when the decimal will be off by .01. That’s why I am trying to hopefully create a VBA that will convert to decimal using the specific criteria I have in another column.

 

[PeteWright]

I have a formula that converts time into decimal. Unfortunately, it doesn’t convert the same as a chart I am using. There are times when the decimal will be off by .01. That’s why I am trying to hopefully create a VBA that will convert to decimal using the specific criteria I have in another column.

See my previous post on VBA code.

Regarding your anwer, could you then share your data (cell formulas etc)?

 

[WhiteRaven76]

=RIGHTSee my previous post on VBA code.

Regarding your anwer, could you then share your data (cell formulas etc)?

Pete,

This is what the sheet looks like now. AD2 would show 06, AE2 would show 00. I am trying to find a way to have a VBA crosscheck AE2 against all of column AF. When it finds the result, it would then take the adjacent number from column AG and combine that with AD2 and output to AH2. So, AH2 would show 06.00, AH3 12.50, AH4 13.33 and so on.

AB	AD	AE	AF	AG
06:00	=LEFT(AB2,FIND("",AB2)+1)	=RIGHT(AB2,FIND("",AB2)+1)	00	.00
12:30	=LEFT(AB3,FIND("",AB3)+1)	=RIGHT(AB3,FIND("",AB3)+1)	01	.02
13:20	=LEFT(AB4,FIND("",AB4)+1)	=RIGHT(AB4,FIND("",AB4)+1)	02	.03
14:35	=LEFT(AB5,FIND("",AB5)+1)	=RIGHT(AB5,FIND("",AB5)+1)	03	.05
06:01	=LEFT(AB6,FIND("",AB6)+1)	=RIGHT(AB6,FIND("",AB6)+1)	04	.07
12:00	=LEFT(AB7,FIND("",AB7)+1)	=RIGHT(AB7,FIND("",AB7)+1)	05	.08
12:30	=LEFT(AB8,FIND("",AB8)+1)	=RIGHT(AB8,FIND("",AB8)+1)	06	.10
14:01	=LEFT(AB9,FIND("",AB9)+1)	=RIGHT(AB9,FIND("",AB9)+1)	07	.12
06:00	=LEFT(AB10,FIND("",AB10)+1)	=RIGHT(AB10,FIND("",AB10)+1)	08	.13
12:36	=LEFT(AB11,FIND("",AB11)+1)	=RIGHT(AB11,FIND("",AB11)+1)	09	.15
			10	.17
			11	.18
			12	.20
			13	.22
			14	.23
			15	.25
			16	.27
			17	.28
			18	.30
			19	.32
			20	.33
			21	.35
			22	.37
			23	.38
			24	.40
			25	.42
			26	.43
			27	.45
			28	.47
			29	.48
			30	.50
			31	.52
			32	.53
			33	.55
			34	.57
			35	.58
			36	.60
			37	.62
			38	.63
			39	.65
			40	.67
			41	.68
			42	.70
			43	.72
			44	.73
			45	.75
			46	.77
			47	.78
			48	.80
			49	.82
			50	.83
			51	.85
			52	.87
			53	.88
			54	.90
			55	.92
			56	.93
			57	.95
			58	.97
			59	.98

 

[WhiteRaven76]

I was able to use the INDEX and MATCH formula to put the correct data into adjacent cells and then a simple =AD2&AJ2 to combine them. I also had to force excel to recognize 0 as a decimal with =TEXT(AI2,".00"). running beautifully now!

=INDEX($AH$2:$AH$61, MATCH(AF2,$AG$2:$AG$61))